x

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
more ▼

asked Aug 25, 2011 at 07:26 AM in Default

David 2 1 gravatar image

David 2 1
412 45 50 52

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Aug 25, 2011 at 07:32 AM

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered Aug 25, 2011 at 08:50 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

+1 I'm with you on the trigger - hate them for business logic processing
Aug 25, 2011 at 01:19 PM Kev Riley ♦♦
Triggers are generally fairly poor for business logic processing, but I have found them very useful for logging purposes.
Aug 25, 2011 at 01:44 PM TimothyAWiseman
Yes for logging/auditing. But a definite no for business rules. (Having that battle in present job at the moment.....)
Aug 25, 2011 at 01:46 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x986
x474
x120

asked: Aug 25, 2011 at 07:26 AM

Seen: 1642 times

Last Updated: Aug 25, 2011 at 07:26 AM