August 14, 2013 at 7:27 am
Is there a way to turn a table trigger off in a stored procedure, then turn it back on after it skips that table.
We have the trigger in place for a 3rd part to insert(trigger point) into that table and automatically the trigger is fired off to inserts into the the database.
August 14, 2013 at 7:39 am
Can you please provide some more detailed information about your probem?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 14, 2013 at 7:54 am
Welcome to the SSC discussion forum!
Can you disable triggers? Yes. Should you? That depends. You want to be sure you understand the reason for it being there before disabling it. You may need child records, audit records or validation to fire, but without any details, it's impossible to say. The best way is to consult the authors of the system. Please be careful - you don't want to break anything.
http://msdn.microsoft.com/en-us/library/ms189748%28v=sql.100%29.aspx/css
August 14, 2013 at 8:09 am
We have a full load process in place to pull comments. We only allow 254 characters before we would write another row with a comment group by column with the number 2. That is so we know these to rows are one comment.
At this point, the comments already exists in the database. When we insert into the table to send it out to the third party, it is trying to insert the same comment again which is the point where it errors out. It errors out because the table trigger is fired off inserting something that already exists.
So I am trying to turn off that trigger on the comment table in the full load process but have it turn back on after the table is populated and the full load is completed. That way the 3rd party can populate the table and have the trigger fire off after the full load is done.
If I disable the trigger on the table in the Object Explorer, it will run fine. I just need to figure out a way to put this is a stored procedure script to disable, then enable at the end.
August 14, 2013 at 8:14 am
cdl_9009 (8/14/2013)
If I disable the trigger on the table in the Object Explorer, it will run fine. I just need to figure out a way to put this is a stored procedure script to disable, then enable at the end.
The link I posted above isn't to the SSMS GUI, but to the SQL command. Please read it (your solution is there) and make sure you understand it before you do it.
August 14, 2013 at 11:33 am
cdl_9009 (8/14/2013)
Is there a way to turn a table trigger off in a stored procedure, then turn it back on after it skips that table.We have the trigger in place for a 3rd part to insert(trigger point) into that table and automatically the trigger is fired off to inserts into the the database.
Yes, by disabling the trigger.
However, you can also simply set a "flag" value that allows *only* action(s) from that session to be skipped/ignored in the trigger. If you want more info on that, let me know.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 14, 2013 at 12:22 pm
I been messing with this more. I didn't know that DISABLE wasn't a keyword so I found out you need a ; after BEGIN. I am testing these different options.
Is there an option that is better than the other. Then I been trying to also test what would be better, one Begin statement or two.
I am trying to write a query that tells me if it is turning on and off at the right times also.
BEGIN;
DISABLE TRIGGER tr_COMMENT ON COMMENT
INSERT INTO COMMENT
SELECT
FROM
WHERE
END
BEGIN;
ENABLE TRIGGER tr_COMMENT ON COMMENT
END
BEGIN
ALTER TABLE COMMENT DISABLE TRIGGER tr_COMMENT
INSERT INTO COMMENT
SELECT
FROM
WHERE
END
BEGIN
ALTER TABLE COMMENT ENABLE TRIGGER tr_COMMENT
END
or
BEGIN
ALTER TABLE COMMENT DISABLE TRIGGER tr_COMMENT
INSERT INTO COMMENT
SELECT
FROM
WHERE
ALTER TABLE COMMENT ENABLE TRIGGER tr_COMMENT
END
August 14, 2013 at 1:07 pm
I think DISABLE is far more dangerous, since it applies to every task modifying the table.
Instead you can use CONTEXT_INFO to selectively exit the trigger / skip all/part of the trigger.
For example, in the calling proc:
--set flag to exclude following UPDATE from all (or some) trigger actions
SET CONTEXT_INFO 0xEE
SELECT case when left(context_info(), 1) = 0xff then 1 else 0 end AS flag_setting
UPDATE dbo.table_with_trigger
SET ... = ...
WHERE ...
--reset flag
SET CONTEXT_INFO 0x00
SELECT case when left(context_info(), 1) = 0xff then 1 else 0 end AS flag_setting
And, in the trigger:
CREATE TRIGGER ...
ON dbo.table_with_trigger
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON
--sql statement(s) to apply to EVERYTHING, regardless of flag(s)
--...
-- leave early if flag is set
IF LEFT(CONTEXT_INFO(), 1) = 0xEE
RETURN;
--sql statement(s) to apply ONLY if flag is OFF
INSERT INTO dbo.log_table
SELECT ...
FROM ...
GO
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply