|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Creating a trigger using a cursorI need to create a trigger on all tables in a database that will insert into an audit table username, and event on the table. I can create the trigger individually, but I would like to put this into a cursor so I do not have to run the trigger 500 times. I am grabbing all user tables and trying to exec a string within the cursor to create the triggers. I keep gettin eror by kyword insert. which I believe is near " INSERT INTO #inputbuffer" Below is the code I am using: TIA, Joe declare @name varchar(100), @str varchar(8000) declare crscall cursor for select name from sysobjects where type = 'u' open crscall fetch next from crscall into @name while @@Fetch_Status = 0 begin declare @str varchar(8000),@name varchar(50) set @name = 'testrights' select @str = 'IF EXISTS (SELECT name FROM sysobjects WHERE name = '+''''+@name+'_Audit_InsUpd'+''''+' AND type = '+''''+'TR'+''''+') DROP TRIGGER Audit_InsUpd' exec (@str) select @str = 'CREATE TRIGGER '+@name+'_Audit_InsUpd ON '+@name+ 'FOR INSERT, UPDATE AS BEGIN SET NOCOUNT ON DECLARE @ExecStr varchar(50), @Qry nvarchar(255) CREATE TABLE #inputbuffer ( EventType nvarchar(30), Parameters int, EventInfo nvarchar(255) ) SET @ExecStr = '+''''+'DBCC INPUTBUFFER('+ STR(@@SPID)+')'+''''+char(13)+ ' INSERT INTO #inputbuffer EXEC (@ExecStr) SET @Qry = (SELECT EventInfo FROM #inputbuffer) insert into Tbl_MSDBAudit select SUSER_SNAME(),@qry END' select @str exec (@str) fetch next from crscall into @name end close crsCAll deallocate crsCAll jaylou wrote on Thu, 28 Jul 2005 07:01:13 -0700:
> Hi all, Did you copy and paste that code? If so, there are 2 errors I spotted > I need to create a trigger on all tables in a database that will insert > into an audit table username, and event on the table. I can create the > trigger individually, but I would like to put this into a cursor so I do > not have to run the trigger 500 times. > I am grabbing all user tables and trying to exec a string within the > cursor to create the triggers. I keep gettin eror by kyword insert. which > I believe is near > " INSERT INTO #inputbuffer" > Below is the code I am using: straight away, both near the word INSERT. Comments inline, look for Typo #1 and Typo #2. Dan Show quote > TIA, Typo #1. There's no space between ' and FOR, so you'd end up with invalid > Joe > > declare @name varchar(100), @str varchar(8000) > > declare crscall cursor for > select name from sysobjects > where type = 'u' > > open crscall > > fetch next from crscall > into @name > > while @@Fetch_Status = 0 > begin > > declare @str varchar(8000),@name varchar(50) > set @name = 'testrights' > select @str = 'IF EXISTS (SELECT name FROM sysobjects > WHERE name = '+''''+@name+'_Audit_InsUpd'+''''+' AND type = > '+''''+'TR'+''''+') > DROP TRIGGER Audit_InsUpd' > exec (@str) > > select @str = 'CREATE TRIGGER '+@name+'_Audit_InsUpd > ON '+@name+ > 'FOR INSERT, UPDATE AS syntax here as the table name will be concatenated into FOR and then the INSERT keyword is invalid as there is no FOR. > BEGIN Typo #2. There's a ' missing at the start of this line, so this INSERT won't > SET NOCOUNT ON > > DECLARE @ExecStr varchar(50), @Qry nvarchar(255) > > CREATE TABLE #inputbuffer > ( > EventType nvarchar(30), > Parameters int, > EventInfo nvarchar(255) > ) > SET @ExecStr = '+''''+'DBCC INPUTBUFFER('+ STR(@@SPID)+')'+''''+char(13)+ > ' INSERT INTO #inputbuffer be inside the string being assigned to @str, it's going to be run in the trigger creating code and #inputbuffer doesn't yet exist as a table. However, I'm pretty sure the error is due to typo #1 otherwise you'd have received an error about table #inputbuffer not existing, the compiler might not be getting this far. Show quote > EXEC (@ExecStr) > > SET @Qry = (SELECT EventInfo FROM #inputbuffer) > > insert into Tbl_MSDBAudit > select SUSER_SNAME(),@qry > > END' > > select @str > exec (@str) > > fetch next from crscall > into @name > > end > close crsCAll > deallocate crsCAll > Thank you daniel,
I guess it was just an extra pair of eyes! The first typo did the trick. Thanks again. Joe Daniel,
I am getting a very strange result from running create trigger in a cursor. It seems if I exec the @str it creates the trigger but the insert into my Audit table brings in the code that creates the trigger not the actual in from the declarations to the end not the actual insert, update statememt as if I create the trigger individually. I even tried to run the @str passing in the parameters manually, but that also brought in the code not the aactual event i am trying to capture. whe I insert into a table it should return my name, my insert statement not the actual code that creates the trigger. Would you know why the string function seems not to work properly? Show quote "Daniel Crichton" wrote: > jaylou wrote on Thu, 28 Jul 2005 07:01:13 -0700: > > > Hi all, > > I need to create a trigger on all tables in a database that will insert > > into an audit table username, and event on the table. I can create the > > trigger individually, but I would like to put this into a cursor so I do > > not have to run the trigger 500 times. > > I am grabbing all user tables and trying to exec a string within the > > cursor to create the triggers. I keep gettin eror by kyword insert. which > > I believe is near > > " INSERT INTO #inputbuffer" > > Below is the code I am using: > > Did you copy and paste that code? If so, there are 2 errors I spotted > straight away, both near the word INSERT. Comments inline, look for Typo #1 > and Typo #2. > > Dan > > > TIA, > > Joe > > > > declare @name varchar(100), @str varchar(8000) > > > > declare crscall cursor for > > select name from sysobjects > > where type = 'u' > > > > open crscall > > > > fetch next from crscall > > into @name > > > > while @@Fetch_Status = 0 > > begin > > > > declare @str varchar(8000),@name varchar(50) > > set @name = 'testrights' > > select @str = 'IF EXISTS (SELECT name FROM sysobjects > > WHERE name = '+''''+@name+'_Audit_InsUpd'+''''+' AND type = > > '+''''+'TR'+''''+') > > DROP TRIGGER Audit_InsUpd' > > exec (@str) > > > > select @str = 'CREATE TRIGGER '+@name+'_Audit_InsUpd > > ON '+@name+ > > 'FOR INSERT, UPDATE AS > > Typo #1. There's no space between ' and FOR, so you'd end up with invalid > syntax here as the table name will be concatenated into FOR and then the > INSERT keyword is invalid as there is no FOR. > > > BEGIN > > SET NOCOUNT ON > > > > DECLARE @ExecStr varchar(50), @Qry nvarchar(255) > > > > CREATE TABLE #inputbuffer > > ( > > EventType nvarchar(30), > > Parameters int, > > EventInfo nvarchar(255) > > ) > > SET @ExecStr = '+''''+'DBCC INPUTBUFFER('+ STR(@@SPID)+')'+''''+char(13)+ > > ' INSERT INTO #inputbuffer > > Typo #2. There's a ' missing at the start of this line, so this INSERT won't > be inside the string being assigned to @str, it's going to be run in the > trigger creating code and #inputbuffer doesn't yet exist as a table. > However, I'm pretty sure the error is due to typo #1 otherwise you'd have > received an error about table #inputbuffer not existing, the compiler might > not be getting this far. > > > EXEC (@ExecStr) > > > > SET @Qry = (SELECT EventInfo FROM #inputbuffer) > > > > insert into Tbl_MSDBAudit > > select SUSER_SNAME(),@qry > > > > END' > > > > select @str > > exec (@str) > > > > fetch next from crscall > > into @name > > > > end > > close crsCAll > > deallocate crsCAll > > > > Hi
Run this Code declare @str varchar(8000),@name varchar(50) declare crscall cursor for select name from sysobjects where type = 'u' open crscall fetch next from crscall into @name while @@Fetch_Status = 0 begin --set @name = 'testrights' select @str = 'IF EXISTS (SELECT name FROM sysobjects WHERE name = '+''''+@name+'_Audit_InsUpd'+''''+' AND type = '+''''+'TR'+''''+') DROP TRIGGER Audit_InsUpd' exec (@str) select @str = 'CREATE TRIGGER '+@name+'_Audit_InsUpd ON '+@name+ ' FOR INSERT, UPDATE AS BEGIN SET NOCOUNT ON DECLARE @ExecStr varchar(50), @Qry nvarchar(255) CREATE TABLE #inputbuffer ( EventType nvarchar(30), Parameters int, EventInfo nvarchar(255) ) SET @ExecStr = '+''''+'DBCC INPUTBUFFER('+ STR(@@SPID)+')'+''''+char(13)+ ' INSERT INTO #inputbuffer EXEC (@ExecStr) SET @Qry = (SELECT EventInfo FROM #inputbuffer) insert into Tbl_MSDBAudit select SUSER_SNAME(),@qry END' select @str exec (@str) fetch next from crscall into @name end close crsCAll deallocate crsCAll With warm regards Jatinder Singh Jatinder,
I am getting a very strange result from running create trigger in a cursor. It seems if I exec the @str it creates the trigger but the insert into my Audit table brings in the code that creates the trigger not the actual in from the declarations to the end not the actual insert, update statememt as if I create the trigger individually. I even tried to run the @str passing in the parameters manually, but that also brought in the code not the aactual event i am trying to capture. whe I insert into a table it should return my name, my insert statement not the actual code that creates the trigger. Would you know why the string function seems not to work properly? Show quote "jsfromynr" wrote: > Hi > > Run this Code > > declare @str varchar(8000),@name varchar(50) > > > declare crscall cursor for > select name from sysobjects > where type = 'u' > > > open crscall > > > fetch next from crscall > into @name > > > while @@Fetch_Status = 0 > begin > --set @name = 'testrights' > select @str = 'IF EXISTS (SELECT name FROM sysobjects > WHERE name = '+''''+@name+'_Audit_InsUpd'+'Â'''+' AND type = > '+''''+'TR'+''''+') > DROP TRIGGER Audit_InsUpd' > exec (@str) > > > select @str = 'CREATE TRIGGER '+@name+'_Audit_InsUpd > ON '+@name+ > ' FOR INSERT, UPDATE AS > BEGIN > SET NOCOUNT ON > > > DECLARE @ExecStr varchar(50), @Qry nvarchar(255) > > > CREATE TABLE #inputbuffer > ( > EventType nvarchar(30), > Parameters int, > EventInfo nvarchar(255) > ) > SET @ExecStr = '+''''+'DBCC INPUTBUFFER('+ > STR(@@SPID)+')'+''''+char(13)+ > ' INSERT INTO #inputbuffer > EXEC (@ExecStr) > > > SET @Qry = (SELECT EventInfo FROM #inputbuffer) > > > insert into Tbl_MSDBAudit > select SUSER_SNAME(),@qry > > > END' > > > select @str > exec (@str) > > > fetch next from crscall > into @name > > > end > close crsCAll > deallocate crsCAll > > > With warm regards > Jatinder Singh > > On Thu, 28 Jul 2005 12:47:03 -0700, jaylou wrote:
>Jatinder, Hi jaylou,>I am getting a very strange result from running create trigger in a cursor. >It seems if I exec the @str it creates the trigger but the insert into my >Audit table brings in the code that creates the trigger not the actual in >from the declarations to the end not the actual insert, update statememt as >if I create the trigger individually. > >I even tried to run the @str passing in the parameters manually, but that >also brought in the code not the aactual event i am trying to capture. whe I >insert into a table it should return my name, my insert statement not the >actual code that creates the trigger. > >Would you know why the string function seems not to work properly? First, change exec (@str) to print @str in the code that creates the triggers. Then carefully review the code that's printed. Is this what you wanted, or does it look strange? If that doesn't help, then undo that change, but now change EXEC (@ExecStr) to PRINT @ExecStr in the generated trigger code. Do some DML to fire the trigger and check the code that's printed. Can you now spot the error? I didn't really dig into this, but my first guess is that you didn't double the quotes enough to postpone the execution of DBCC INPUTBUFFER until the trigger execution. Am I right? You might also find the following an interesting read: http://www.sommarskog.se/dynamic_sql.html Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) Hi jaylou,
The problem is with @@spid . when you ran the automated procedure at that time @@spid represents id of process not that of trigger in which you wish to use it . Further more '@@spid' should be passed as string not as value. USE THIS AND Try may this solve your problem DECLARE @ExecStr varchar(50), @Qry nvarchar(255) CREATE TABLE #inputbuffer ( EventType nvarchar(30), Parameters int, EventInfo nvarchar(255) ) -- NEW ADDITION SET @ExecStr = '+''''+'DBCC INPUTBUFFER(@@SPID)'+''''+char(13)+ ' INSERT INTO #inputbuffer EXEC (@ExecStr) Hop it works With warm regards Jatinder Singh Are you sure you really want this kind of automation?
Anyway, change the script to print out the query strings instead of just executing them. Then test them: parse them and attempt to execute them. And when it's done - I don't want to scare you - you'll still have to test them 500 times. ML This is a one-time thing to create all the triggers so it won't get into
production code. So good for the poster if he can automate the creation of the triggers. But I agree with out on the last part, he's still have to test them all. Maybe he can automate that part too. ;-) Show quote "ML" <M*@discussions.microsoft.com> wrote in message news:8C32588A-D032-40D3-B554-D6A64EB83F15@microsoft.com... > Are you sure you really want this kind of automation? > > Anyway, change the script to print out the query strings instead of just > executing them. Then test them: parse them and attempt to execute them. > > And when it's done - I don't want to scare you - you'll still have to test > them 500 times. > > > ML If he puts his mind to it, someday his entire life might get automated. :)
He'll have automated himself out of existence. ML Well, I wish you good luck on your journey. :)
I hope those 500 tables weren't created automatically by mistake... ;) ML Have you looked at third part audit tools that will not hurt
performance?  |
|||||||||||||||||||||||
Other interesting topics