CODEWITHSUNDEEP
sql-servert-sqltriggers
mo alaz
mo alaz

Reputation: 4749

SQL Server Triggers: Update

Using Microsoft SQL Server, I am writing a SQL trigger for an update on a table and I am stuck. I am not very proficient in SQL, so it may be something basic that I am missing.

CREATE TRIGGER test
ON  tableName
AFTER UPDATE
AS 
BEGIN
DECLARE @variableA int
SELECT @variableA = variableA FROM DELETED

DECLARE @variableB int
SELECT @variableB = variableB FROM INSERTED;

f(@variableA <> @variableB )
BEGIN
//Do What I want
END

This works correctly, as it preforms the action when the two variables are different. However, I do not want to consider ALL records from tableName.

I wrote the following to get only the entries that I want.

WITH
table (variableID, variable)
AS
(
    SELECT variableID, variable
    FROM tableName
    WHERE variable= 'value'
)

SELECT * FROM table

So what I want is to apply the trigger ONLY to the values that are found in the SELECT. Am I going about this the right way?

Upvotes: 0

Views: 139

Answers (1)

Dan
Dan

Reputation: 4502

An UPDATE query isn't guaranteed to only impact a single row at a time, and your queries against INSERTED and DELETED need to reflect that to be on the safe side. Probably the easiest way to detect a changed value is to join the two trigger tables on the primary key.

CREATE TRIGGER test ON tableName
AFTER UPDATE AS 
BEGIN
  IF EXISTS (
    SELECT * FROM INSERTED I
      INNER JOIN DELETED D ON I.variableID = D.variableID
      WHERE D.VariableA <> I.VariableB
        /* AND further conditions */
  ) BEGIN
    -- Perform your action
  END
END

Where this snippet has /* AND further conditions */ would be a good place to insert the additional checks you want to do against the data before running your action.

For example, you can limit your action to updates where variable was 'value' before the update...

AND D.variable = 'value'

or where variable is set to 'value' by the update...

AND I.variable = 'value'

Upvotes: 2

Related Questions