Databases
StackOverflow2013
Postsdbo
POTrigger to fire only if a condition is met in SQL Server
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTrigger to fire only if a condition is met in SQL Server
    text
    copied!<p>I hope this is a simple enough question for any SQL people out there...</p> <p>We have a table which hold system configuration data, and this is tied to a history table via triggers so we can track who changed what, and when. </p> <p>I have a requirement to add another value in to this table, but it is one that will change frequently from code, and has a requirement that we don't track it's history (we don't want to clog the table with many thousands of updates per day.</p> <p>At present, our trigger is a little like this...</p> <pre><code>CREATE TRIGGER [dbo].[SystemParameterInsertUpdate] ON [dbo].[SystemParameter] FOR INSERT, UPDATE AS BEGIN SET NOCOUNT ON INSERT INTO SystemParameterHistory ( Attribute, ParameterValue, ParameterDescription, ChangeDate ) SELECT Attribute, ParameterValue, ParameterDescription, ChangeDate FROM Inserted AS I END </code></pre> <p>I'd like to be able to add some logic to stop it creating the record if an Attribute colum value is prefixed with a specific string (e.g. "NoHist_")</p> <p>Given that I have almost no experience working with triggers, I was wondering how it would be best to implement this... I have tried a where clause like the following</p> <pre><code>where I.Attribute NOT LIKE 'NoHist_%' </code></pre> <p>but it doesn't seem to work. The value is still copied over into the history table.</p> <p>Any help you could offer would be appreciated.</p> <hr> <p>OK - as predicted by Cade Roux, this fail spectacularly on multiple updates. I'm going to have to take a new approach to this. Does anyone have any other suggestions, please?</p> <hr> <p>Guys - Please educate me here... Why would LEFT() be preferable to LIKE in this scenario? I know I've accepted the answer, but I'd like to know for my own education. </p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload