CODEWITHSUNDEEP
sqlsql-server
Paks
Paks

Reputation: 1470

Prevent trigger from firing

I have the following trigger

First trigger:

ALTER TRIGGER [dbo].[DIENSTLEISTUNG_Update]
ON [dbo].[DIENSTLEISTUNG]
INSTEAD OF UPDATE
AS

BEGIN
    SET  NOCOUNT ON;
    DECLARE @intNewID int

         INSERT INTO [DIENSTLEISTUNG] 
         (DESCRIPTION, QUANTITY, 
         PRICE, AZ_MO, AZ_DI,AZ_MI,AZ_DO,AZ_FR,
         AZ_SA,AZ_SO,DIENSTLEISTUNGSART_ID,
         UPDATE_USER, UPDATE_DATE,
         PERMISSIONS, KONTRAKTPOSITION,ITEMNUMBER,
         PRIORITY, VALID)

         SELECT  i.DESCRIPTION, i.QUANTITY, i.PRICE, i.AZ_MO, 
         i.AZ_DI,i.AZ_MI,i.AZ_DO,i.AZ_FR,       
         i.AZ_SA,i.AZ_SO,i.SERVICETYPE_ID, i.UPDATE_USER,GETDATE(),
         i.PERMISSIONS, i.KONTRAKTPOSITION,i.ITEMNUMBER, i.PRIORITY, 'Y'

         FROM INSERTED i
         JOIN deleted d ON i.ID=d.ID
         WHERE i.PRICE<>d.PRICE
         or i.DESCRIPTION<>d.DESCRIPTION


         IF ( UPDATE (PRICE) OR UPDATE (DESCRIPTION) )
         UPDATE  S
         SET  s.VALID = 'N'    
         FROM SERVICE s
         JOIN INSERTED i ON I.ID = S.ID


         IF UPDATE(PRIORITY)
         UPDATE s
         SET s.PRIORITY= i.PRIORITY
         FROM SERVICE s
         JOIN INSERTED i ON i.ID = s.ID

   SET NOCOUNT OFF;      
END

The first Trigger copies an entire row with a new ID if a change in the original row happens, also the trigger set a flag. The old row gets the flag VALID = 'N' and the new row gets the flag VALID = 'Y'. The trigger only creates a new row if PRICE or DESCRIPTION are updated. So far so good.

My problem is that if I want to update the PRIORITY in the new row the trigger fires again and sets the flag to VALID = 'N'. That should not happen. I want only to update the priority without creating a new row or update a another column.

Thanks for help

Upvotes: 5

Views: 3791

Answers (3)

Asad Ali Malik
Asad Ali Malik

Reputation: 1

What you can do is set the context info of the session which you are in like this:

SET Context_Info 0x55555

And then in your trigger check for the context info to decide what to do:

DECLARE @Cinfo VARBINARY(128)  
SELECT @Cinfo = Context_Info()  
IF @Cinfo = 0x55555  
    RETURN

Upvotes: 0

woony
woony

Reputation: 250

You can make triggers fire only on certain columns or one colomn. like this.

CREATE TRIGGER tr_something ON myTable
FOR INSERT, UPDATE
AS
IF UPDATE(myColumn)
BEGIN
--       do what you want
END

post below gives more details, seems I'm to slow :)

Upvotes: 1

marc_s
marc_s

Reputation: 754488

You cannot prevent a trigger from firing - if it's present and not disabled, it will fire. That's how triggers work.

What you can do is check inside your trigger which columns have been updated. So you could do something like this in your one single trigger:

CREATE TRIGGER [dbo].[DIENSTLEISTUNG_Update]
ON [dbo].[DIENSTLEISTUNG]
FOR UPDATE
AS
    IF UPDATE(PRICE) 
      ... (do what you need to do if PRICE is updated)...

    IF UPDATE(DESCRIPTION)
      ... (do what you need to do if DESCRIPTION is updated)...

    IF UPDATE(PRIORITY)
      ... (do what you need to do if PRIORITY is updated)...

Use the UPDATE() function to check whether a given column has been updated - and if so, act on it. See the MSDN docs on how to use the UPDATE() function.

Upvotes: 3

Related Questions