Auditing tables

Hi ,
I am trying trigger for after delete /insert and update .
using below trigger , unable to insert data in myaudittable ..
Pls suggest , what changes should I make ?
Regards
"swati" <swati.zingade@.ugamsolutions.com> wrote in message news:...
> Thanks!! It works properly .
>
> Regards,
> Swati
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ebvGMQ1uEHA.4084@.TK2MSFTNGP10.phx.gbl...
> > swati
> > create trigger tru_MyTable on MyTable after update
> > as
> >
> > if @.@.ROWCOUNT = 0
> > return
> >
> > insert MyAuditTable
> > select
> > i.ID
> > , d.MyColumn
> > , i.MyColumn
> > from
> > inserted i
> > join
> > deleted d on d.ID = o.Id
> > go
> >
> > "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> > news:uNbsEK1uEHA.1308@.TK2MSFTNGP09.phx.gbl...
> > > HI!
> > >
> > > I want to audit some collumns of one of my huge table . I wrote a
> trigger
> > > for copying updated data and original data in audit table.
> > > But when I try to update multiple rows , it gives error of "cannot
> > update
> > > multiple rows. "
> > >
> > > Is there any easy way of auditing tables without using triggers . I
> donot
> > > want to use C2-audit .
> > >
> > > Regards,
> > > Swati
> > >
> > >
> > >
> > >
> > >
> >
> >
>
>What error are you getting? Please post your actual trigger code and table
DDL.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:O$InOPAvEHA.1520@.TK2MSFTNGP11.phx.gbl...
> Hi ,
> I am trying trigger for after delete /insert and update .
> using below trigger , unable to insert data in myaudittable ..
> Pls suggest , what changes should I make ?
> Regards
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message news:...
>> Thanks!! It works properly .
>> Regards,
>> Swati
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:ebvGMQ1uEHA.4084@.TK2MSFTNGP10.phx.gbl...
>> > swati
>> > create trigger tru_MyTable on MyTable after update
>> > as
>> >
>> > if @.@.ROWCOUNT = 0
>> > return
>> >
>> > insert MyAuditTable
>> > select
>> > i.ID
>> > , d.MyColumn
>> > , i.MyColumn
>> > from
>> > inserted i
>> > join
>> > deleted d on d.ID = o.Id
>> > go
>> >
>> > "swati" <swati.zingade@.ugamsolutions.com> wrote in message
>> > news:uNbsEK1uEHA.1308@.TK2MSFTNGP09.phx.gbl...
>> > > HI!
>> > >
>> > > I want to audit some collumns of one of my huge table . I wrote a
>> trigger
>> > > for copying updated data and original data in audit table.
>> > > But when I try to update multiple rows , it gives error of "cannot
>> > update
>> > > multiple rows. "
>> > >
>> > > Is there any easy way of auditing tables without using triggers . I
>> donot
>> > > want to use C2-audit .
>> > >
>> > > Regards,
>> > > Swati
>> > >
>> > >
>> > >
>> > >
>> > >
>> >
>> >
>>
>|||Thanks dan . There was some silly mistake I did in coding . Instead of using
full outer joins ,I was using normal joins ,so was not able to get the
deleted records .Now it's working fine.
regards,
Swati.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ulLkEBCvEHA.2196@.TK2MSFTNGP14.phx.gbl...
> What error are you getting? Please post your actual trigger code and
table
> DDL.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:O$InOPAvEHA.1520@.TK2MSFTNGP11.phx.gbl...
> > Hi ,
> > I am trying trigger for after delete /insert and update .
> > using below trigger , unable to insert data in myaudittable ..
> > Pls suggest , what changes should I make ?
> >
> > Regards
> >
> >
> > "swati" <swati.zingade@.ugamsolutions.com> wrote in message news:...
> >> Thanks!! It works properly .
> >>
> >> Regards,
> >> Swati
> >>
> >> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> >> news:ebvGMQ1uEHA.4084@.TK2MSFTNGP10.phx.gbl...
> >> > swati
> >> > create trigger tru_MyTable on MyTable after update
> >> > as
> >> >
> >> > if @.@.ROWCOUNT = 0
> >> > return
> >> >
> >> > insert MyAuditTable
> >> > select
> >> > i.ID
> >> > , d.MyColumn
> >> > , i.MyColumn
> >> > from
> >> > inserted i
> >> > join
> >> > deleted d on d.ID = o.Id
> >> > go
> >> >
> >> > "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> >> > news:uNbsEK1uEHA.1308@.TK2MSFTNGP09.phx.gbl...
> >> > > HI!
> >> > >
> >> > > I want to audit some collumns of one of my huge table . I wrote a
> >> trigger
> >> > > for copying updated data and original data in audit table.
> >> > > But when I try to update multiple rows , it gives error of
"cannot
> >> > update
> >> > > multiple rows. "
> >> > >
> >> > > Is there any easy way of auditing tables without using triggers . I
> >> donot
> >> > > want to use C2-audit .
> >> > >
> >> > > Regards,
> >> > > Swati
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> >
> >> >
> >>
> >>
> >
> >
>

Audit Delete Statements

Hi

I was curious whether it's possible to audit DELETE statements in the MS SQL database. I created a procedure (below), but I didn't find any event associated with DELETE statements.

Any help will be greatly appreciated!

Thanks,
Alla

CREATE proc sp_Turn_Audit_On
as
/************************************************** **/
/* Created by: SQL Profiler */
/* Date: 11/15/2006 05:16:40 PM */
/************************************************** **/

-- Create a Queue
declare @.rc int
declare @.TraceID int
declare @.maxfilesize bigint
declare @.StatusMsg varchar
declare @.ServerTraceFile varchar
set @.ServerTraceFile = 'E:\Program Files\Microsoft SQL Server\MSSQL\Trace\Audit_Info'
set @.maxfilesize = 1024

-- Client side File and Table cannot be scripted

-- Set the events
declare @.on bit
set @.on = 1

exec @.rc = sp_trace_create @.TraceID OUTPUT, 0, N'\\hostname\dbauditlog\my_dir', @.maxfilesize, NULL
print @.TraceID

if (@.rc != 0) goto error
exec sp_trace_setevent @.TraceID, 14, 1, @.on
exec sp_trace_setevent @.TraceID, 14, 6, @.on
exec sp_trace_setevent @.TraceID, 14, 9, @.on
exec sp_trace_setevent @.TraceID, 14, 10, @.on
exec sp_trace_setevent @.TraceID, 14, 11, @.on
exec sp_trace_setevent @.TraceID, 14, 12, @.on
exec sp_trace_setevent @.TraceID, 14, 13, @.on
exec sp_trace_setevent @.TraceID, 14, 14, @.on
exec sp_trace_setevent @.TraceID, 14, 16, @.on
exec sp_trace_setevent @.TraceID, 14, 17, @.on
exec sp_trace_setevent @.TraceID, 14, 18, @.on
-- Set the Filters
declare @.intfilter int
declare @.bigintfilter bigint

exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'

-- Set the trace status to start
exec sp_trace_setstatus @.TraceID, 1
--SELECT @.StatusMsg = 'sp_trace_setstatus' + ' Error - ' + @.TraceID
-- display trace id for future references
select TraceID=@.TraceID

goto noCursor

error:
select ErrorCode=@.rc

noCursor:
return

GO
exec sp_procoption N'sp_Turn_Audit_On', N'startup', N'true'
GOTake a look at tirggers (http://doc.ddart.net/mssql/sql70/create_8.htm) :)|||I see...

I just noticed that there is an event to audit TSQL via SQL Profiler. I spooled the script to a SQL file. however, is there any way of filtering it that it would capture DELETE statements only?

Thanks,
Alla|||Spool SQL Profiler output to a table so you can query it.