Update Trigger on a Composite Key 1

[rickj65]

I'm trying to write an update trigger for use on a 2-column composite key. This has to be easier then I'm wondering.

Anyway, if I use the statement...

"IF UPDATE(Column1) AND UPDATE(Column2)..."

then it is required that both columns be updated, otherwise the trigger won't fire.

If I use the statement...

"IF UPDATE(Column1) OR UPDATE(Column2)..."

This seems like it would work, right? Or is there another way of handling this?

I just want to know if I'm using the correct approach to the problem.

Thanks.

 

[SQLSister]

If inserted.Column1 <> deleted.Column1 or inserted.Column2 <> deleted.Column2 is probably what you want. This checks to see if either Column1 or Column2 changed. Triggers have two pseudotables called inserted and deleted which store the data which has been changed temporarily. It is these tables you access to determine what was changed, inserted or deleted in order to do further processing based on what happened. In an update trigger both tables will come into play, deleted having the old information and inserted the new.

Of course, I generally try to have primary key columns that will never change, but sometimes that may not be possible.

If you do allow the change to these columns, then make sure that you adequately handle all the places where this may be a foreign key. It is easy to create a table later that uses this as a foreign key and forget to change the trigger thereby having your data get inretrievably messed up. Also remember that cascading update and delete are better choices than triggers if they will work in your circumstances.

If you are writing this trigger to check for changes to these fields so you can forbid them, then good for you. But you should also make sure the user interface forbids the changes as the users will get the message without having to send the whole record for processing. Usually I have the user interface developers put all validation on their end to reduce network traffic. If no changes are allowed, putting a text box on the form which is not editable is the best choice. Then users know they can't even try to do this. The trigger is still a good idea in this case, because you may have someone directly try to change data from the tables instead of the user interface (like developers!).

 

[rickj65]

Thanks for the detailed response.

I am using deleted and inserted tables to enforce RI and I'm OK with writing the code to enforce RI. However, I was uncertain on how to initiate the trigger with the composite key and make sure that I've accounted for all possibilities of any column in the composite key changing (unfortunately this DB allows for the key fields changing values).

It eventually occurred to me that I have to write two separate IF UPDATE() statements within the trigger for each column in the composite key.

Anyway, you did write something that I want to follow up on...&quot;Also remember that cascading update and delete are better choices than triggers if they will work in your circumstances&quot;

Maybe I'm missing something, but I am enforcing cascading updates and deletes using triggers. Is there another way to do this outside of triggers that I'm unaware of?

TIA,
Rick

PS - You get a star for your help and detailed answer!