Re: Trigger Problems with UPDATE()

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 05/12/04 

Date: Wed, 12 May 2004 14:47:43 -0400

That's different. The IF UPDATE() construct only tests if the given column
was mentioned in the SET clause of the UPDATE statement that fired the
trigger. It does not check if the value was indeed changed. Your trigger
code should look like:

CREATE TRIGGER [MasterBF_UPDATE] ON dbo.MasterBF
FOR UPDATE
AS
IF @@ROWCOUNT = 0
    RETURN

IF UPDATE(BFOwnerID)
INSERT INTO ActivityLog(ActivityType,
PrimaryKeyValue, TableName)
SELECT CASE WHEN d.BFOwnerID = i.BFOwnerID
            THEN 'UPDATE'
            ELSE 'TRANSFER'
            END, BFID, 'MasterBF'
FROM inserted i
JOIN deleted d on d.PrimaryKeyValue = i.PrimaryKeyValue
GO 

-- 
Tom
---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
<anonymous@discussions.microsoft.com> wrote in message
news:c09e01c4384f$0c806750$a401280a@phx.gbl...
I don't understand your responce.
Please explain in laymen terms.
I expected the TRIGGER to do exactly as I wrote "If the
BFOwnerID field was updated, insert 'TRANSFER', else
insert 'UPDATE'." But it's not doing that.
TIA,
Eric
>-----Original Message-----
>This trigger will insert a 'TRANSFER' if the UPDATE
statement had BFOwnerID
>mentioned in the SET clause - regardless of the value to
which it is being
>set.  If you leave it out of the SET clause, then
an 'UPDATE' will be
>inserted.
>
>What behavior did you expect?
>
>-- 
>Tom
>
>----------------------------------------------------------
-----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>
>"Eric D." <anonymous@discussions.microsoft.com> wrote in
message
>news:c2a901c4384c$017c74a0$a001280a@phx.gbl...
>Hi,
>
>I'm having a problem with this code and I don't understand
>why.
>
>Code:
>=======================
>CREATE TRIGGER [MasterBF_UPDATE] ON dbo.MasterBF
>FOR UPDATE
>AS
>IF UPDATE(BFOwnerID)
>BEGIN
>INSERT INTO ActivityLog(ActivityType,
>PrimaryKeyValue, TableName)
>SELECT 'TRANSFER', BFID, 'MasterBF'
>FROM INSERTED
>END
>ELSE
>BEGIN
>INSERT INTO ActivityLog(ActivityType,
>PrimaryKeyValue, TableName)
>SELECT 'UPDATE', BFID, 'MasterBF'
>FROM INSERTED
>END
>=======================
>
>The problem arises in the ELSE statement. It never runs.
>Is there a reason?
>
>TIA,
>Eric
>
>
>
>
>
>.
>

Relevant Pages

  • Re: Update Trigger Fires Twice
    ... you post your DDL and the update statement here. ... Which SQL Server version are you using? ... > CREATE TRIGGER MakeHistory ON current ... > It appears that the trigger fires twice, the first time is correct, but ...
    (microsoft.public.sqlserver.programming)
  • Re: Trigger Problems with UPDATE()
    ... This trigger will insert a 'TRANSFER' if the UPDATE statement had BFOwnerID ... If you leave it out of the SET clause, ... SELECT 'TRANSFER', BFID, 'MasterBF' ...
    (microsoft.public.sqlserver.programming)
  • Re: Trigger Problems with UPDATE()
    ... I expected the TRIGGER to do exactly as I wrote "If the ... BFOwnerID field was updated, insert 'TRANSFER', else ... If you leave it out of the SET clause, ... >PrimaryKeyValue, TableName) ...
    (microsoft.public.sqlserver.programming)
  • Re: Trigger Problem
    ... > If I statically store an update statement in a stored ... > software the trigger partially works correctly. ... and see the difference in what SQL Server receives in each ...
    (microsoft.public.sqlserver.programming)
  • Re: Records not inserting...
    ... > I am totally new to SQL server. ... However, in a trigger, would I be ... test the effects of certain data modifications and to set conditions for ... UPDATE statements. ...
    (microsoft.public.sqlserver.odbc)