question

David 2 1 avatar image
David 2 1 asked

Creating An Update Insert Trigger On A Specific Column That Fires On Entry Of A Specific Character

Is it possible to create an insert update trigger that only executes on the entry of a specific character in a specific column? For example, I have a column in the CLIENT table called SEND_INFO. I want to create a trigger that ONLY fires is an existing client record in that table has their SEND_INFO value updated to 'Y', or when a new client record is inserted into the CLIENT table with the SEND_INFO value of 'Y'. TIA
t-sqlsql-server-2000trigger
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
You can't control whether the trigger fires based on data, but you could of course use logic within the trigger code to branch depending on the value of a column, and in the case of the update test the previous value of the column in the `deleted` table to see if the value has changed.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
Personally I would be reluctant to put this sort of logic into a trigger. To be fair I am reluctant to use triggers at all but that's another issue. This could be done using service broker which would disconnect the processes of the data transaction and the logic you want to enforce. If you use a trigger and the logic is flawed it can easily cause locking and blocking because the transaction will be slowed.
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Triggers are generally fairly poor for business logic processing, but I have found them very useful for logging purposes.
1 Like 1 ·
+1 I'm with you on the trigger - hate them for business logic processing
0 Likes 0 ·
Yes for logging/auditing. But a definite no for business rules. (Having that battle in present job at the moment.....)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.