Creating a trigger using a cursor

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:
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 crsCAllAre 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|||Thank you daniel,
I guess it was just an extra pair of eyes!
The first typo did the trick.
Thanks again.
Joe|||Thank you! this was helpful as well as Daniels.|||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. ;-)
"ML" <ML@.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|||That's what I am looking for. Automation is a wonderful thing!|||Well, I wish you good luck on your journey. :)
I hope those 500 tables weren't created automatically by mistake... ;)
ML|||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 =3D 'u'
open crscall
fetch next from crscall
into @.name
while @.@.Fetch_Status =3D 0
begin
--set @.name =3D 'testrights'
select @.str =3D 'IF EXISTS (SELECT name FROM sysobjects
WHERE name =3D '+''''+@.name+'_Audit_InsUpd'+'=AD'''+' AND type =3D
'+''''+'TR'+''''+')
DROP TRIGGER Audit_InsUpd'
exec (@.str)
select @.str =3D '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 =3D '+''''+'DBCC INPUTBUFFER('+
STR(@.@.SPID)+')'+''''+char(13)+
' INSERT INTO #inputbuffer
EXEC (@.ExecStr)
SET @.Qry =3D (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=20
deallocate crsCAll=20
With warm regards
Jatinder Singhsql