Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
proone23
Starting Member
10 Posts |
 Posted - 2011-08-12 : 09:52:40
|
| At the beginning of a functioning SP, I insert this line of code:if not exists (select * from sys.triggers where name = 'deleteinstead') BEGIN CREATE TRIGGER DELETEINSTEAD ON ACCESS_LOG_ DECLARE @audit_id VARCHAR(20) DECLARE @user_id VARCHAR(20) SELECT @audit_id = AUDIT_ID FROM DELETED SELECT @user_id = USER_ID FROM DELETED UPDATE ACCESS_LOG_ SET ACTIVE_IND = 'N', MODIFIED_BY = 0, MODIFY_TIMESTAMP = GETDATE() WHERE AUDIT_ID = @audit_id ENDWhen I check it, I get:Msg 156, Level 15, State 1, Procedure AUDIT_USER, Line 31Incorrect syntax near the keyword 'TRIGGER'.Msg 134, Level 15, State 1, Procedure AUDIT_USER, Line 36The variable name '@audit_id' has already been declared. Variable names must be unique within a query batch or stored procedure.Msg 134, Level 15, State 1, Procedure AUDIT_USER, Line 38The variable name '@user_id' has already been declared. Variable names must be unique within a query batch or stored procedure.It is true that I am using @user_id somewhere else in the SP but I can't change that. Is there someway to insert a block of code to create a trigger if it does not already exist?Thanks in advance! |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2011-08-12 : 10:42:38
|
| To start with, your CREATE TRIGGER syntax is not correct, and wouldn't run either inside or outside of a stored procedure.Fix that first.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
 |
|
|
|
|
|
|
|
Can I create a database trigger from within a SP?