Alter Trigger Assistance
-
Hi,
I am trying to create a stored procedure to run through all the tables I have with triggers already enabled and disable them. I keep getting errors when trying to create the sp.
My code:
CREATE PROC sp_disableTriggers
AS
DECLARE
@temp_tablename VARCHAR(255)
, @temp_triggername VARCHAR(255)
, @statustype INT
DECLARE c_disableTrigger CURSOR
FOR
SELECT SubString(S2.Name,1,30) as [Table]
, SubString(S.Name, 1, 30) as [Trigger]
, CASE (SELECT -- Correlated subquery
OBJECTPROPERTY(OBJECT_ID(S.Name), 'ExecIsTriggerDisabled'))
WHEN 0 THEN 'Enabled'
WHEN 1 THEN 'Disabled'
END as Status
FROM Sysobjects S
JOIN Sysobjects S2 ON s.parent_obj = S2.ID
WHERE s.Type = 'TR'
OPEN c_disableTrigger
FETCH c_disableTrigger INTO @temp_tablename, @temp_triggername, @statustype
WHILE (@@fetch_status = 0)
IF @statustype = 0
BEGIN
ALTER TABLE @temp_tablename
DISABLE TRIGGER @temp_triggername
END
FETCH c_disableTrigger INTO @temp_tablename, @temp_triggername, @statustype
END
CLOSE c_disableTrigger
DEALLOCATE c_disableTrigger
********************
Error Message:
Msg 102, Level 15, State 1, Procedure sp_disableTriggers, Line 27
Incorrect syntax near '@temp_tablename'.
Msg 156, Level 15, State 1, Procedure sp_disableTriggers, Line 33
Incorrect syntax near the keyword 'CLOSE'.
I am guessing the CLOSE error is due to the error on temp_tablename.
Any comments are welcome.
Thanks,
______________________________
AJ Mendo | @SQLAJ -
You can't use a variable name to reference the tables/triggers. You'd have to parse it in using dynamic SQL.
My concern is, however, that usually having to disable triggers like this is a sign of either bad application design, bad database design, or both.
-
Aaron,
Thanks for your comment about the tables/trigger names.
But I posted my code for assistance NOT negative comments.
You would be more helpful to leave them out.
______________________________
AJ Mendo | @SQLAJ -
I figured if I was in your shoes, I'd rather reconsider the potential ramifications of potentially (granted, I haven't seen your app or db) problematic design now rather than when it truly causes issues down the line. Take my experience as you will, however.
-
Point taken.
Respectfully
______________________________
AJ Mendo | @SQLAJ -
I didn't think Aaron was being negative. It sounds like a perceptive observation to me. Look up EXEC in BOL. You need to build your ALTER TABLE statement into a variable and run it through EXEC or sp_executesql.
-
It wasn't negative!!! It was friendly advise that you're probably commiting some form of SQL suicide by needing to do this!!!
There's only one reason to do such a thing and you didn't mention why you needed to do this. Instead of getting in a huff about negative comments, maybe you should evaluate what was said and ask a thoughtful "What do you mean"? Maybe even learn how to post a complete scenario
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply