• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 381
  • Last Modified:

disable a trigger inside another trigger

I'm trying to get an AFTER UPDATE trigger to disable another trigger, perform an update on another table, the enable the trigger it disabled. But I can't get it to work.

My code is:
ALTER TRIGGER [dbo].[Parcel_RefundCheck]
   ON  [dbo].[Parcel]
   AFTER UPDATE
AS
DECLARE @newParNo varchar(35),
      @oldParNo varchar(35),
      @taxyear smallint
BEGIN
SET NOCOUNT ON;
-- if the ParcelNo changed
IF NOT EXISTS ( SELECT 1
      FROM DELETED d JOIN
                 INSERTED i on d.ParcelNo = i.ParcelNo)
      BEGIN
      DISABLE TRIGGER Refund_RefundCheck_Update ON Refund;
      set @oldParNo = (select ParcelNo from DELETED)
      set @taxyear = (select TaxYear from DELETED)
      set @newParNo = (select ParcelNo from INSERTED)
      UPDATE      RefundCheck SET ParcelNo = @newParNo
            WHERE ParcelNo = @oldParNo and TaxYear = @taxyear;
      ENABLE TRIGGER Refund_RefundCheck_Update ON Refund;
      END
END

If I manually disable the trigger Refund_RefundCheck_Update, the new trigger works ok and after running my "UPDATE PARCEL" statement, I see that the trigger Refund_RefundCheck_Update is enabled again. But I can't get the trigger to disable it, run the update, then enable it again.

Is there a way to make this happen?
0
GordonPrince
Asked:
GordonPrince
  • 3
1 Solution
 
kamindaCommented:
Instead of using DISABLE TRIGGER and ENABLE TRIGGER try using ALTER TABLE to do this.

i.e

ALTER TABLE dbo.Refund DISABLE TRIGGER Refund_RefundCheck_Update  ;

AND

ALTER TABLE dbo.Refund ENABLE TRIGGER Refund_RefundCheck_Update  ;
0
 
GordonPrinceAuthor Commented:
No difference using ALTER TABLE... instead of DISABLE TRIGGER
0
 
GordonPrinceAuthor Commented:
I tried putting the DISABLE statement in my code, to execute the disable trigger statement, then run my update query, then enable the trigger. Doing that works, although your suggestion helps. ALTER TABLE DISABLE works, DISABLE TRIGGER doesn't work. So I think your syntax is better.
0
 
GordonPrinceAuthor Commented:
This seems to work finally. I created a loginless user "PTGspecial" and granted it ALTER permissions on the table. Then I run this set of SQL statements.
EXECUTE AS USER = 'PTGspecial';
ALTER TABLE dbo.Refund DISABLE TRIGGER Refund_RefundCheck_Update;
UPDATE Parcel set ParcelNo = 'xxx' where ParcelID = 12345;
ALTER TABLE Refund ENABLE TRIGGER Refund_RefundCheck_Update;
REVERT;

Open in new window

0
 
Alpesh PatelAssistant ConsultantCommented:
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now