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,
Is there a way to make this happen?
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,
Is there a way to make this happen?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
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;
ASKER