February 1, 2011 at 10:52 am
Hello - I have a delete trigger on a table in one database that I want to trigger a delete on a table in a different database, both in the same instance of SQL 2000. I want to prevent the first trigger from firing the delete trigger in the second database.
After looking through previous posts I have tried a few variations to filter on nest level but none of them work. first i tried code like this:
ALTER TRIGGER [dbo].[UPDATE_EMAIL_IN_HEAT] ON [dbo].[CONTSUPP]
FOR DELETE
AS
BEGIN TRANSACTION
IF trigger_nestlevel() <1
begin
[snip]
This had no effect, the second trigger kept firing.
More recently I tried
ALTER TRIGGER [dbo].[UPDATE_EMAIL_IN_HEAT] ON [dbo].[CONTSUPP]
FOR DELETE
AS
BEGIN TRANSACTION
IF @@NESTLEVEL > 1
BEGIN
RETURN
END
[snip]
This time the delete doesn't occur in the second db and it locks up for a long period of time.
I would rather not use sp_configure to remove recursive triggers altogether since this is an instance wide change, I don't think it would have an effect but would have to do more digging to know. Instead, I would prefer an approach that recognizes if the deletion is caused by the trigger in DB1 Table 1 and then don't fire the delete trigger in DB2 Table 2 (but DO complete the deletion!) Is there a way to do this with a differnt syntax or expression?
If none of this works I can disable and then enable the triggers in the DB2 as part of the trigger in DB1 (and vice versa), this seems to work fine but it would be preferable to keep them enabled and just don't fire if the delete comes from the trigger in the other DB.
Any help is appreciated - thanks!
February 2, 2011 at 11:17 am
why did you begin a transaction in you second try? you're already within a transaction.
February 2, 2011 at 11:23 am
I was just copying a snippet I saw somewhere else. If you think it will work without the BEGIN and END I will try that.
February 2, 2011 at 12:32 pm
Not "Begin", "Begin Transaction" isn't needed.
February 2, 2011 at 12:45 pm
Here's some context. The T-SQL for altering the existing trigger has this block of code:
========
ALTER TRIGGER [dbo].[UPDATE_EMAIL_IN_HEAT] ON [dbo].[CONTSUPP]
FOR DELETE
AS
BEGIN TRANSACTION
IF @@NESTLEVEL > 1
BEGIN
RETURN
END
--Scenario 1
begin
update heat_test.gmdev.Contacts set emailID = null
where custid in
(select accountno from deleted where
deleted.contact = 'E-mail Address'
and deleted.mergecodes = 'HEAT1'
and deleted.linkacct is null)
and contacttype='Primary Contact'
--update log for scenario 1
insert into gm_heat_deletions_log
(trigger_name,gm_account,heat_custid,contsupref, gm_rectype,trigger_time,by_whom)
select 'UPDATE_EMAIL_IN_HEAT',accountno,accountno,contsupref, 'DELETE EMAIL FOR PRIMARY',getDATE(),HOST_NAME()
from deleted where
deleted.contact = 'E-mail Address'
and deleted.mergecodes = 'HEAT1'
and deleted.linkacct is null
end
=========
there are a bunch more begin...end sections, then the TSQL ends with COMMIT.
Are you saying I should put the code
IF @@NESTLEVEL > 1
BEGIN
RETURN
END
before BEGIN TRANSACTION - or in place of it? I suspect I am not using the write syntax in the right order.
February 3, 2011 at 1:43 am
I think Steve was trying to tell you that the BEGIN TRANSACTION is not necessary at all. Once you are inside the trigger, you are automatically inside a transaction anyway.
I'm not going to be much help, as I'm not proposing a solution to your problem, but I would like to point out a potential pitfall in your proposed solution.
I would steer clear of using @@NESTLEVEL in this way or you may find the trigger in the 2nd database not firing when you would expect it to. For example:-
UPDATE CONTSUPP blah blah blah
This would fire your trigger and it would go ahead and do its business, but if I update the table from inside a stored procedure:-
CREATE PROC X
AS
UPDATE CONTSUPP blah blah blah
GO
EXEC X
The trigger will fire, but your check for @@NESTLEVEL will incorrectly stop it from doing its business.
February 3, 2011 at 2:22 am
Hi, did you try this?
IF TRIGGER_NESTLEVEL()>1 RETURN
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 3, 2011 at 3:30 am
mister.magoo (2/3/2011)
Hi, did you try this?
IF TRIGGER_NESTLEVEL()>1 RETURN
A bit less risky, but could still suffer from similar problems to @@NESTLEVEL if the trigger is ultimately fired as a result of updates from a trigger on another table in the same database.
I may be being a bit paranoid, but I'm speaking from experience of inheriting a system that suffered from exactly this kind of problem.
February 3, 2011 at 4:21 am
To add some more context to the trigger would help...
In your first database, use
DECLARE @ci VARBINARY(128) = CAST(DB_NAME() AS VARBINARY(128));
SET CONTEXT_INFO @ci;
And in the second, use:
IF CONVERT(VARCHAR(50),CONTEXT_INFO()) <> DB_NAME() AND trigger_nestlevel () > 1 RETURN;
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 3, 2011 at 7:50 am
How would the context setting be placed? For example, should it work if my first trigger (modify stmt) starts out like this:
======
ALTER TRIGGER [dbo].[UPDATE_EMAIL_IN_HEAT] ON [dbo].[CONTSUPP]
FOR DELETE
AS
BEGIN TRANSACTION
DECLARE @ci VARBINARY(128) = CAST(DB_NAME() AS VARBINARY(128));
SET CONTEXT_INFO @ci;
IF CONVERT(VARCHAR(50),CONTEXT_INFO()) <> DB_NAME() AND trigger_nestlevel () > 1 RETURN;
--Scenario 1
begin
update heat_test.gmdev.Contacts set emailID = null
where custid in
(select accountno from deleted where
deleted.contact = 'E-mail Address'
and deleted.mergecodes = 'HEAT1'
and deleted.linkacct is null)
and contacttype='Primary Contact'
and the second trigger in the other db (modify) starts like this:
ALTER TRIGGER [gmdev].[DELETE_EMAIL_IN_GOLDMINE2] ON [gmdev].[Contacts]
FOR DELETE
AS
BEGIN TRANSACTION
DECLARE @ci VARBINARY(128) = CAST(DB_NAME() AS VARBINARY(128));
SET CONTEXT_INFO @ci;
IF CONVERT(VARCHAR(50),CONTEXT_INFO()) <> DB_NAME() AND trigger_nestlevel () > 1 RETURN;
declare @custidexists varchar(100)
declare @contactidexists varchar(100)
set @contactidexists = null
set @contactidexists = (select top 1 contactid from deleted where custid is not null)
set @custidexists = null
set @custidexists = (select top 1 custid from deleted where custid is not null)
if @contactidexists is not null OR @custidexists is not null
BEGIN
???
February 3, 2011 at 11:33 am
Can I step back and ask why you need this? Not questioning you, but trying to help think a little outside the box about what might work.
My first thought is that you might want to add a column to the table that has a default value. If you are updating from another database, insert some marker in the column the trigger can read.
February 3, 2011 at 11:37 am
ae_from_pa (2/3/2011)
How would the context setting be placed? For example, should it work if my first trigger (modify stmt) starts out like this:======
ALTER TRIGGER [dbo].[UPDATE_EMAIL_IN_HEAT] ON [dbo].[CONTSUPP]
FOR DELETE
AS
BEGIN TRANSACTION
DECLARE @ci VARBINARY(128) = CAST(DB_NAME() AS VARBINARY(128));
SET CONTEXT_INFO @ci;
-- In this simple case you dont need this
-- if this trigger needs the functionality then it gets slightly more complex
-- let me know if two way checking is required...
--IF CONVERT(VARCHAR(50),CONTEXT_INFO()) <> DB_NAME() AND trigger_nestlevel () > 1 RETURN;
--Scenario 1
begin
update heat_test.gmdev.Contacts set emailID = null
where custid in
(select accountno from deleted where
deleted.contact = 'E-mail Address'
and deleted.mergecodes = 'HEAT1'
and deleted.linkacct is null)
and contacttype='Primary Contact'
and the second trigger in the other db (modify) starts like this:
ALTER TRIGGER [gmdev].[DELETE_EMAIL_IN_GOLDMINE2] ON [gmdev].[Contacts]FOR DELETE
AS
BEGIN TRANSACTION
-- No, if you set the CONTEXT_INFO here you wipe out the one set in the first trigger : commented out
-- DECLARE @ci VARBINARY(128) = CAST(DB_NAME() AS VARBINARY(128));
-- SET CONTEXT_INFO @ci;
IF CONVERT(VARCHAR(50),CONTEXT_INFO()) <> DB_NAME() AND trigger_nestlevel () > 1 RETURN;
declare @custidexists varchar(100)
declare @contactidexists varchar(100)
set @contactidexists = null
set @contactidexists = (select top 1 contactid from deleted where custid is not null)
set @custidexists = null
set @custidexists = (select top 1 custid from deleted where custid is not null)
if @contactidexists is not null OR @custidexists is not null
BEGIN
???
I have amended your code (with a couple of comments). You just set CONTEXT_INFO in the primary trigger and read it in the secondary.
If you need two way checking i.e both triggers need to check whether to fire or not, then it becomes just a case of first checking if there is a value in CONTEXT_INFO() first.
Oh and get rid of those BEGIN TRANSACTION statements - they spell trouble...
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 3, 2011 at 11:41 am
OF course, if those are the real triggers, then are you not showing us the delete from the "other" database table in the first trigger?
Just checking because it won't work if don't set the CONTEXT_INFO in the correct place to start with...
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 3, 2011 at 12:06 pm
Yes, two way checking is needed - deletes could start in either database, i want to have the second trigger not fire if the first one has already fired, otherwise it is trying to delete a record that was already deleted in the first database.
So...if I understand this right I need to reverse the statements...so the first trigger looks like
ALTER TRIGGER [dbo].[UPDATE_EMAIL_IN_HEAT] ON [dbo].[CONTSUPP]
FOR DELETE
AS
IF CONVERT(VARCHAR(50),CONTEXT_INFO()) <> DB_NAME() AND trigger_nestlevel () > 1 RETURN;
ELSE
DECLARE @ci VARBINARY(128) = CAST(DB_NAME() AS VARBINARY(128));
SET CONTEXT_INFO @ci;
--Scenario 1
begin
update heat_test.gmdev.Contacts set emailID = null
where custid in
(select accountno from deleted where
deleted.contact = 'E-mail Address'
and deleted.mergecodes = 'HEAT1'
and deleted.linkacct is null)
and contacttype='Primary Contact'
and the second one goes like this...???
ALTER TRIGGER [gmdev].[DELETE_EMAIL_IN_GOLDMINE2] ON [gmdev].[Contacts]
FOR DELETE
AS
IF CONVERT(VARCHAR(50),CONTEXT_INFO()) <> DB_NAME() AND trigger_nestlevel () > 1 RETURN;
DECLARE @ci VARBINARY(128) = CAST(DB_NAME() AS VARBINARY(128));
SET CONTEXT_INFO @ci;
declare @custidexists varchar(100)
declare @contactidexists varchar(100)
set @contactidexists = null
set @contactidexists = (select top 1 contactid from deleted where custid is not null)
set @custidexists = null
set @custidexists = (select top 1 custid from deleted where custid is not null)
if @contactidexists is not null OR @custidexists is not null
BEGIN
Would this work?
I have also cut out the BEGIN TRANSACTION in both triggers.
February 3, 2011 at 12:24 pm
Would it work? That all depends - if those were the only two triggers in use then yes - if you get rid of the ELSE - it is not needed.
Generally, if you want to prevent nested triggers from firing if the originating action( whether it be an insert, a delete or an update) was in another database then you need to check , set, and check again, like this :
CREATE TRIGGER db1_trigger1 ON db1.schema.table AFTER INSERT,DELETE,UPDATE
AS
DECLARE @ci VARBINARY(128) = CAST(DB_NAME() AS VARBINARY(128));
-- Check if anything has already set the CONTEXT_INFO and if not set it to my current database,
IF CONTEXT_INFO() IS NULL
SET CONTEXT_INFO @ci;
-- Check CONTEXT_INFO against my database and check the TRIGGER_NESTLEVEL
-- IF CONTEXT_INFO does not equal my database and TRIGGER_NESTLEVEL>1 just return
IF CONVERT(VARCHAR(50),CONTEXT_INFO()) <> DB_NAME() AND trigger_nestlevel () > 1 RETURN;
This code can be the same in all triggers that take part in these rules.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply