I am learning triggers for the first time in SQL, and I am unsure of how to write/phrase a certain trigger.
I am attempting to make a trigger that would check to see if a value within an attempted row insert is greater than the existing value within the table, and then update the value if the new value being added to the table is greater than the current version of that value. So if a user were to try to insert a row containing an attribute with a value of 3 in table Z that contains that same attribute but with a value of 1, the values would be compared then table Z would be updated to have that attribute now have a value of 3.
Advertisement
Answer
Re your (now edited out) question about :old and :new – old and new are most applicable to update queries where they allow you to examine the before-update and after-update value of the same row – the scenario you’re discussing is one of editing a different row using an insert so :old doesn’t really apply – the insert trigger will only have a :new pseudorow. There wouldn’t be an :old that refers to a different row existing in the table
Two ways you could do this with a “before insert” trigger – either:
- find the existing row and if it exists, copy its data into the :new row then delete the existing row. This is an errorless way of handling the issue but is perhaps longer to code
- issue an update query to update an existing row and if a row is updated, raise an application error to prevent the insert of the new row
For a discussion on before insert triggers that prevent inserts in some scenarios see Prevent Insert Trigger – and expect some said “don’t use triggers” chatter; it’s good advice. Every time I’ve used triggers a part of me wishes I hadn’t 🙂
ps; I specifically haven’t written the code for this for you because you’ve said it’s a learning exercise, but if you feel you’ll learn more by reading code from someone else rather than writing it out yourself, let me know and I’ll provide some example. Tejash has made a reasonable start on the update route i describe, it just remains to use the sql%rowcount to know how many rows were updated and raise an exception to prevent the insert