I've inherited a server where auto create stats and auto update stats have b
een turned off. There is a weekly job which executes Update Statistics on e
ach table with the norecompute clause. In looking at traces I'm seeing quit
e a few missing statistics
entries.
When is it appropriate to turn off auto create stats?Peter,
as I understand it, if you've just created a table and are loading it with
loads of rows, you might disable the creation of statistics because the
performance overhead is more than you can afford. Likewise if the table
already exists with statistics and requires loading, you might run
sp_autostats or the UPDATE STATISTICS command with the WITH NORECOMPUTE
option. However, this is only a temporary measure and once loaded it is
normal to have it auto updating, or else incorrect query plans may result.
Regards,
Paul Ibison
Showing posts with label update. Show all posts
Showing posts with label update. Show all posts
Tuesday, March 27, 2012
Auto Create Stats & Auto Update stats
I've inherited a server where auto create stats and auto update stats have been turned off. There is a weekly job which executes Update Statistics on each table with the norecompute clause. In looking at traces I'm seeing quite a few missing statistics
entries.
When is it appropriate to turn off auto create stats?
Peter,
as I understand it, if you've just created a table and are loading it with
loads of rows, you might disable the creation of statistics because the
performance overhead is more than you can afford. Likewise if the table
already exists with statistics and requires loading, you might run
sp_autostats or the UPDATE STATISTICS command with the WITH NORECOMPUTE
option. However, this is only a temporary measure and once loaded it is
normal to have it auto updating, or else incorrect query plans may result.
Regards,
Paul Ibison
entries.
When is it appropriate to turn off auto create stats?
Peter,
as I understand it, if you've just created a table and are loading it with
loads of rows, you might disable the creation of statistics because the
performance overhead is more than you can afford. Likewise if the table
already exists with statistics and requires loading, you might run
sp_autostats or the UPDATE STATISTICS command with the WITH NORECOMPUTE
option. However, this is only a temporary measure and once loaded it is
normal to have it auto updating, or else incorrect query plans may result.
Regards,
Paul Ibison
Auto Create Stats
Is it a good idea to have Auto create stats and Auto
update stats ON in a database
Does it slow down performance while loading large amounts
of data
SanjayHi Sanjay
No, the updating of stats does not happen during a load. When you run a
query, and SQL Server notices the stats are out of date, then it will auto
update them.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:000a01c39cb9$c906e160$a601280a@.phx.gbl...
> Is it a good idea to have Auto create stats and Auto
> update stats ON in a database
> Does it slow down performance while loading large amounts
> of data
> Sanjay|||Sanjay,
Nothing is free, so Auto Create Stats does cost something from the server.
However, I understand that it tries to do that work during low periods and
stay out of the way of other work. And remember, statistics that are not in
sync with the data can cause the optimizer to guess wrong.
There is a discussion of the issues at:
http://www.sql-server-performance.com/statistics.asp
Russell Fields
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:000a01c39cb9$c906e160$a601280a@.phx.gbl...
> Is it a good idea to have Auto create stats and Auto
> update stats ON in a database
> Does it slow down performance while loading large amounts
> of data
> Sanjaysql
update stats ON in a database
Does it slow down performance while loading large amounts
of data
SanjayHi Sanjay
No, the updating of stats does not happen during a load. When you run a
query, and SQL Server notices the stats are out of date, then it will auto
update them.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:000a01c39cb9$c906e160$a601280a@.phx.gbl...
> Is it a good idea to have Auto create stats and Auto
> update stats ON in a database
> Does it slow down performance while loading large amounts
> of data
> Sanjay|||Sanjay,
Nothing is free, so Auto Create Stats does cost something from the server.
However, I understand that it tries to do that work during low periods and
stay out of the way of other work. And remember, statistics that are not in
sync with the data can cause the optimizer to guess wrong.
There is a discussion of the issues at:
http://www.sql-server-performance.com/statistics.asp
Russell Fields
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:000a01c39cb9$c906e160$a601280a@.phx.gbl...
> Is it a good idea to have Auto create stats and Auto
> update stats ON in a database
> Does it slow down performance while loading large amounts
> of data
> Sanjaysql
Monday, March 19, 2012
auditting select statement
We have triggers written for insert/update/deletes of data, now there is a
new requirement to audit select statements ran against the database.
I know SQL Profiler shows the select statements ran. I was wondering if
anyone has suggestions on how to somehow use that function(or any other idea
)
and incorparate it into tracking all select statements?Tracey wrote:
> We have triggers written for insert/update/deletes of data, now there is a
> new requirement to audit select statements ran against the database.
> I know SQL Profiler shows the select statements ran. I was wondering if
> anyone has suggestions on how to somehow use that function(or any other id
ea)
> and incorparate it into tracking all select statements?
We monitor everything that happens in our production databases, by
running server-side traces 24x7 into rotating trace log files. Easily
done using sp_trace_create, etc., providing you have the disk space to
store the accumulating trace log files.
You get the added benefit of being able to analyze your database
activity to look for poorly performing queries.|||SQL Server does not track this activity. Profiler can see it because it
views all commands going into the database. Some options:
(1) If you deny SELECT access to tables and views, and force all access
through stored procedures, it is trivial to log this.
(2) You can have a trace running all the time that dumps data into trace
table(s).
(3) Or you can look at 3rd party tools (in which case, you won't have to
write all of the reporting over the trace table(s)). For example,
Lumigent's Audit DB or Log Explorer. See
http://www.aspfaq.com/search.asp?q=lumigent
"Tracey" <Tracey@.discussions.microsoft.com> wrote in message
news:F7DB3821-3537-493A-A19F-8C17D5A44799@.microsoft.com...
> We have triggers written for insert/update/deletes of data, now there is a
> new requirement to audit select statements ran against the database.
> I know SQL Profiler shows the select statements ran. I was wondering if
> anyone has suggestions on how to somehow use that function(or any other
> idea)
> and incorparate it into tracking all select statements?|||Tracey
Take a look at Dejan's example
For example, lets say we want to follow selects on the Customers table of
the Northwind database. Create a trace with only the following settings:
- SP:StmtCompleted and SQL: StmtCompleted events
- EventClass, TextData, ApplicationName and SPID columns
- DatabaseID Equals 6 (DB_ID() of the Northwind database) and
TextData Like select%customers% filters
- Name the trace SelectTrigger and save it to a table with the same
name in the Northwind database.
Start the trace, and create the following trigger using Query Analyzer:
CREATE TRIGGER TraceSelectTrigger ON SelectTrigger
FOR INSERT
AS
EXEC master.dbo.xp_logevent 60000, 'Select from Customers happened!',
warning
Now check how trigger works by performing couple of selects:
SELECT TOP 1 *
FROM Customers
SELECT TOP 1 *
FROM Orders
SELECT TOP 1 c.CustomerID
FROM Customers c INNER JOIN Orders o
ON c.CustomerID=o.CustomerID
With Event Viewer, check whether you got two warnings in the Application log
for the 1st and the 3rd queries (the 2nd should be filtered out).
"Tracey" <Tracey@.discussions.microsoft.com> wrote in message
news:F7DB3821-3537-493A-A19F-8C17D5A44799@.microsoft.com...
> We have triggers written for insert/update/deletes of data, now there is a
> new requirement to audit select statements ran against the database.
> I know SQL Profiler shows the select statements ran. I was wondering if
> anyone has suggestions on how to somehow use that function(or any other
> idea)
> and incorparate it into tracking all select statements?|||Hi Uri,
Seems good.. But is it fool -proof.
what will happen if I query this way.. just for an arguement
SELECT TOP 1 [Customers List].Customer_ID
FROM Orders as [Customers List]
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Uri Dimant" wrote:
> Tracey
> Take a look at Dejan's example
> For example, let’s say we want to follow selects on the Customers table
of
> the Northwind database. Create a trace with only the following settings:
> - SP:StmtCompleted and SQL: StmtCompleted events
> - EventClass, TextData, ApplicationName and SPID columns
> - DatabaseID Equals 6 (DB_ID() of the Northwind database) and
> TextData Like select%customers% filters
> - Name the trace SelectTrigger and save it to a table with the sa
me
> name in the Northwind database.
> Start the trace, and create the following trigger using Query Analyzer:
>
> CREATE TRIGGER TraceSelectTrigger ON SelectTrigger
> FOR INSERT
> AS
> EXEC master.dbo.xp_logevent 60000, 'Select from Customers happened!',
> warning
>
> Now check how trigger works by performing couple of selects:
>
> SELECT TOP 1 *
> FROM Customers
> SELECT TOP 1 *
> FROM Orders
> SELECT TOP 1 c.CustomerID
> FROM Customers c INNER JOIN Orders o
> ON c.CustomerID=o.CustomerID
>
> With Event Viewer, check whether you got two warnings in the Application l
og
> for the 1st and the 3rd queries (the 2nd should be filtered out).
>
> "Tracey" <Tracey@.discussions.microsoft.com> wrote in message
> news:F7DB3821-3537-493A-A19F-8C17D5A44799@.microsoft.com...
>
>|||Hi
This one will also be logged to the Apllication Viewer, however I agree that
this method is not perfect
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:499B5B18-A216-4732-BF8A-6B295C23D7D1@.microsoft.com...
> Hi Uri,
> Seems good.. But is it fool -proof.
> what will happen if I query this way.. just for an arguement
> SELECT TOP 1 [Customers List].Customer_ID
> FROM Orders as [Customers List]
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "Uri Dimant" wrote:
>
new requirement to audit select statements ran against the database.
I know SQL Profiler shows the select statements ran. I was wondering if
anyone has suggestions on how to somehow use that function(or any other idea
)
and incorparate it into tracking all select statements?Tracey wrote:
> We have triggers written for insert/update/deletes of data, now there is a
> new requirement to audit select statements ran against the database.
> I know SQL Profiler shows the select statements ran. I was wondering if
> anyone has suggestions on how to somehow use that function(or any other id
ea)
> and incorparate it into tracking all select statements?
We monitor everything that happens in our production databases, by
running server-side traces 24x7 into rotating trace log files. Easily
done using sp_trace_create, etc., providing you have the disk space to
store the accumulating trace log files.
You get the added benefit of being able to analyze your database
activity to look for poorly performing queries.|||SQL Server does not track this activity. Profiler can see it because it
views all commands going into the database. Some options:
(1) If you deny SELECT access to tables and views, and force all access
through stored procedures, it is trivial to log this.
(2) You can have a trace running all the time that dumps data into trace
table(s).
(3) Or you can look at 3rd party tools (in which case, you won't have to
write all of the reporting over the trace table(s)). For example,
Lumigent's Audit DB or Log Explorer. See
http://www.aspfaq.com/search.asp?q=lumigent
"Tracey" <Tracey@.discussions.microsoft.com> wrote in message
news:F7DB3821-3537-493A-A19F-8C17D5A44799@.microsoft.com...
> We have triggers written for insert/update/deletes of data, now there is a
> new requirement to audit select statements ran against the database.
> I know SQL Profiler shows the select statements ran. I was wondering if
> anyone has suggestions on how to somehow use that function(or any other
> idea)
> and incorparate it into tracking all select statements?|||Tracey
Take a look at Dejan's example
For example, lets say we want to follow selects on the Customers table of
the Northwind database. Create a trace with only the following settings:
- SP:StmtCompleted and SQL: StmtCompleted events
- EventClass, TextData, ApplicationName and SPID columns
- DatabaseID Equals 6 (DB_ID() of the Northwind database) and
TextData Like select%customers% filters
- Name the trace SelectTrigger and save it to a table with the same
name in the Northwind database.
Start the trace, and create the following trigger using Query Analyzer:
CREATE TRIGGER TraceSelectTrigger ON SelectTrigger
FOR INSERT
AS
EXEC master.dbo.xp_logevent 60000, 'Select from Customers happened!',
warning
Now check how trigger works by performing couple of selects:
SELECT TOP 1 *
FROM Customers
SELECT TOP 1 *
FROM Orders
SELECT TOP 1 c.CustomerID
FROM Customers c INNER JOIN Orders o
ON c.CustomerID=o.CustomerID
With Event Viewer, check whether you got two warnings in the Application log
for the 1st and the 3rd queries (the 2nd should be filtered out).
"Tracey" <Tracey@.discussions.microsoft.com> wrote in message
news:F7DB3821-3537-493A-A19F-8C17D5A44799@.microsoft.com...
> We have triggers written for insert/update/deletes of data, now there is a
> new requirement to audit select statements ran against the database.
> I know SQL Profiler shows the select statements ran. I was wondering if
> anyone has suggestions on how to somehow use that function(or any other
> idea)
> and incorparate it into tracking all select statements?|||Hi Uri,
Seems good.. But is it fool -proof.
what will happen if I query this way.. just for an arguement
SELECT TOP 1 [Customers List].Customer_ID
FROM Orders as [Customers List]
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Uri Dimant" wrote:
> Tracey
> Take a look at Dejan's example
> For example, let’s say we want to follow selects on the Customers table
of
> the Northwind database. Create a trace with only the following settings:
> - SP:StmtCompleted and SQL: StmtCompleted events
> - EventClass, TextData, ApplicationName and SPID columns
> - DatabaseID Equals 6 (DB_ID() of the Northwind database) and
> TextData Like select%customers% filters
> - Name the trace SelectTrigger and save it to a table with the sa
me
> name in the Northwind database.
> Start the trace, and create the following trigger using Query Analyzer:
>
> CREATE TRIGGER TraceSelectTrigger ON SelectTrigger
> FOR INSERT
> AS
> EXEC master.dbo.xp_logevent 60000, 'Select from Customers happened!',
> warning
>
> Now check how trigger works by performing couple of selects:
>
> SELECT TOP 1 *
> FROM Customers
> SELECT TOP 1 *
> FROM Orders
> SELECT TOP 1 c.CustomerID
> FROM Customers c INNER JOIN Orders o
> ON c.CustomerID=o.CustomerID
>
> With Event Viewer, check whether you got two warnings in the Application l
og
> for the 1st and the 3rd queries (the 2nd should be filtered out).
>
> "Tracey" <Tracey@.discussions.microsoft.com> wrote in message
> news:F7DB3821-3537-493A-A19F-8C17D5A44799@.microsoft.com...
>
>|||Hi
This one will also be logged to the Apllication Viewer, however I agree that
this method is not perfect
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:499B5B18-A216-4732-BF8A-6B295C23D7D1@.microsoft.com...
> Hi Uri,
> Seems good.. But is it fool -proof.
> what will happen if I query this way.. just for an arguement
> SELECT TOP 1 [Customers List].Customer_ID
> FROM Orders as [Customers List]
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "Uri Dimant" wrote:
>
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
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> >
> >> >
> >>
> >>
> >
> >
>
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
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> >
> >> >
> >>
> >>
> >
> >
>
Sunday, March 11, 2012
Auditing DB Changes
It seems to me there are two common strategies for doing DB audit trails via a trigger:
1. On an update to a row, duplicate that row in another table withidentical rows, except for perhaps the extra columns which representchange date and changed by. Eg. When there is an update to the Customertable, record the changes in Customer_Audit.
2. On an update to a row, check which fields were updated. Then in acommon audit table record the table, row ID, field, previous value andnew value of the field.
I'm wondering about the pros and cons of each. More specifically, dothe pros and cons change if you are using an O/R mapper (I'm usingNHibernate.)
Some thoughts on method 1 . It seems nice for an O/R mapper, since youcould have, say, a CustomerAudit Object inherit from your Customerobject and just add the properties change date and changed by. Aproblem with this is you're going to add a whole lot of objects - onefor each object which you want to audit. Another drawback is that itcould be difficult to generate a history for a particular propertywhich was updated. Let's say I want to see the history of changes tothe customer's status. I have to load a collection of CustomerAuditobjects (which could be costly). Then I have to iterate through themand compare the status properties to generate a history of statuses.This is a pretty labor instensive method if you compare it to method 2,where the change is recorded by field, not row.
Some thoughts on method 2. It's nice since the changes are by field,not row, which (as above) makes generating a history easy. On the otherhand, you can never have a snapshot of a particular object at aparticular point in time. Moreover, I'm not sure how foreign keys wouldbe handled elegantly. I record that customer.statusID changes from 3 to6. I'd have to do a seperate join to the customerstatus table to getmeaning for 3 and 6 (which method 1 would do automatically).
Thoughts? Any preferred way to do this with an O/R mapper?
Thanks
1. On an update to a row, duplicate that row in another table withidentical rows, except for perhaps the extra columns which representchange date and changed by. Eg. When there is an update to the Customertable, record the changes in Customer_Audit.
2. On an update to a row, check which fields were updated. Then in acommon audit table record the table, row ID, field, previous value andnew value of the field.
I'm wondering about the pros and cons of each. More specifically, dothe pros and cons change if you are using an O/R mapper (I'm usingNHibernate.)
Some thoughts on method 1 . It seems nice for an O/R mapper, since youcould have, say, a CustomerAudit Object inherit from your Customerobject and just add the properties change date and changed by. Aproblem with this is you're going to add a whole lot of objects - onefor each object which you want to audit. Another drawback is that itcould be difficult to generate a history for a particular propertywhich was updated. Let's say I want to see the history of changes tothe customer's status. I have to load a collection of CustomerAuditobjects (which could be costly). Then I have to iterate through themand compare the status properties to generate a history of statuses.This is a pretty labor instensive method if you compare it to method 2,where the change is recorded by field, not row.
Some thoughts on method 2. It's nice since the changes are by field,not row, which (as above) makes generating a history easy. On the otherhand, you can never have a snapshot of a particular object at aparticular point in time. Moreover, I'm not sure how foreign keys wouldbe handled elegantly. I record that customer.statusID changes from 3 to6. I'd have to do a seperate join to the customerstatus table to getmeaning for 3 and 6 (which method 1 would do automatically).
Thoughts? Any preferred way to do this with an O/R mapper?
Thanks
nick7272:
It seems to me there are two common strategies for doing DB audit trails via a trigger:
1. On an update to a row, duplicate that row in another table with identical rows, except for perhaps the extra columns which represent change date and changed by. Eg. When there is an update to the Customer table, record the changes in Customer_Audit.
2. On an update to a row, check which fields were updated. Then in a common audit table record the table, row ID, field, previous value and new value of the field.
...
Nick7272 --
I am surprised there were no replies to your post.
You have the gist of the standard analysis above. Well done.
I want to pick up the topic because I am about to build yet another auditing system.
I think that there is at least one other option in addition to the ones you mention above. It is similar to similar to option 1. Note that in option 1, a developer must propogate schema changes in the tables-to-be-audited into the tables-doing-the-auditing. That can be a chore. I have implemented option 1 and it works OK for < 50 tables or so. More than that and it could be a problem if the schema changes. I am thinking that Option 3 (as yet never implemented by me) one could avoid the need for schema synchronization by using XML. Serialize an object to XML and store that. Sure, an audit recored for an object saved 1 year ago might have a different schema from an object saved 1 day ago, but all of the data would be there. I would be ugly; but, it would "mineable". It would tough to search; but, it would require no schema synchronizatoin. And so on. The problem would come from the complexity of the objects being serialized. Maybe the object could have a custom SerializeEx method that would be a lightweight version of the object, with just the necessary field names and data. Maybe. Anyway, I just wanted to mention this.
Regarding your post, I am wondering...
Since you made the post, have you discovered anything new?
What did you choose for a solution?
Please advise.
Thank you.
-- Mark Kamoski
Thursday, March 8, 2012
Audit triggers problem
Im using triggers to track changes Insert/Update/Deletes on my DB tables and they work for when i am manually adding/editing and deleting a single records.
The problem arises in that I have an asset/inventory management app that dumps lots of details into my DB tables at once each time its run.
Not all of the tables are updated and data cannot be completely inserted.
This is the trigger i have been using - could someone tell me how to modify it to work.
/*
This trigger audit trails all changes made to a table.
It will place in the table Audit all inserted, deleted, changed columns in the table on which it is placed.
It will put out an error message if there is no primary key on the table
You will need to change @.TableName to match the table to be audit trailed
*/
ALTER trigger tr_TableName
on dbo.TableName for insert, update, delete
as
declare @.bit int ,
@.field int ,
@.maxfield int ,
@.char int ,
@.fieldname varchar(128) ,
@.TableName varchar(128) ,
@.PKCols varchar(1000) ,
@.sql varchar(2000),
@.UpdateDate varchar(21) ,
@.Action nvarchar(50) ,
@.HostName nvarchar(50),
@.PKFieldName varchar (1000)
IF EXISTS(SELECT * FROM inserted)
IF EXISTS(SELECT * FROM deleted)
--update = inserted and deleted tables both contain data
BEGIN
SET @.Action = 'UPDATE'
SELECT @.DeviceID = (SELECT inserted.DeviceID FROM inserted INNER JOIN deleted ON inserted.deviceID = deleted.deviceid)
END
ELSE
--insert = inserted contains data, deleted does not
BEGIN
SET @.Action = 'INSERT'
select @.DeviceID = (SELECT DeviceID from inserted)
END
ELSE
--delete = deleted contains data, inserted does not
BEGIN
SET @.Action = 'DELETE'
select @.DeviceID = (SELECT DeviceID from deleted)
END
select @.TableName = 'TableName'
-- date
select @.HostName = host_name(),
@.UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114),
--@.DeviceID,
@.PKFieldName=(select top 1 c.COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,INFORMATION_SCHEMA.KEY_COLUMN_USAGE c where pk.TABLE_NAME = @.TableName
and CONSTRAINT_TYPE = 'PRIMARY KEY' and c.TABLE_NAME = pk.TABLE_NAME and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME)
-- get list of columns
select * into #ins from inserted
select * into #del from deleted
-- Get primary key columns for full outer join
select @.PKCols = coalesce(@.PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @.TableName
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
if @.PKCols is null
begin
raiserror('no PK on table %s', 16, -1, @.TableName)
return
end
select @.field = 0, @.maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @.TableName
while @.field < @.maxfield
begin
select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @.TableName and ORDINAL_POSITION > @.field
select @.bit = (@.field - 1 )% 8 + 1
select @.bit = power(2,@.bit - 1)
select @.char = ((@.field - 1) / 8) + 1
--if substring(COLUMNS_UPDATED(),@.char, 1) & @.bit > 0
begin
select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @.TableName and ORDINAL_POSITION = @.field
select @.sql = 'insert LITE_Inventory (TableName, FieldName, OldValue, NewValue, UpdateDate, Action, Host, PkFieldName, DeviceID)'
-- select @.sql = 'insert LITE_Inventory (TableName, FieldName, OldValue, NewValue, UpdateDate, Action, Host, PkFieldName)'
select @.sql = @.sql + ' select ''' + @.TableName + ''''
select @.sql = @.sql + ',''' + @.fieldname + ''''
select @.sql = @.sql + ',convert(varchar(1000),d.' + @.fieldname + ')'
select @.sql = @.sql + ',convert(varchar(1000),i.' + @.fieldname + ')'
select @.sql = @.sql + ',''' + @.UpdateDate + ''''
select @.sql = @.sql + ',''' + @.Action + ''''
select @.sql = @.sql + ',''' + @.HostName + ''''
select @.sql = @.sql + ',''' + @.PKFieldName + ''''
select @.sql = @.sql + ' from #ins i full outer join #del d'
select @.sql = @.sql + @.PKCols
select @.sql = @.sql + ' where i.' + @.fieldname + ' <> d.' + @.fieldname
select @.sql = @.sql + ' or (i.' + @.fieldname + ' is null and d.' + @.fieldname + ' is not null)'
select @.sql = @.sql + ' or (i.' + @.fieldname + ' is not null and d.' + @.fieldname + ' is null)'
exec (@.sql)
end
endYou have to remember that inerted and deleted can have a set of data...not just 1 row...
Tracking chnages should br pretty straighty forward...
If the data is altered just move the whole row to history...|||You have to remember that inerted and deleted can have a set of data...not just 1 row...
Tracking chnages should br pretty straighty forward...
If the data is altered just move the whole row to history...
How do 'move the whole row to history' - im not sure.
The reason im using this trigger is that it automatically inserts data into a single audit table from all the tables where is has been applied. I can then show this audit table to my users though a GUI.|||USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE myTable99 (Col1 int IDENTITY(1,1), Col2 char(1), ModifiedDate datetime)
CREATE TABLE myAudit99 (AuditAddDate datetime DEFAULT GetDate(), Col1 int, Col2 char(1), ModifiedDate datetime)
GO
CREATE TRIGGER myTrigger99 ON myTable99
FOR UPDATE, DELETE
AS
INSERT INTO myAudit99(Col1, Col2, ModifiedDate)
SELECT Col1, Col2, ModifiedDate FROM deleted
GO
INSERT INTO myTable99(Col2)
SELECT 'x'
GO
SELECT * FROM myTable99
SELECT * FROM myAudit99
GO
UPDATE myTable99 SET Col2 = 'A', ModifiedDate = GetDate()
GO
SELECT * FROM myTable99
SELECT * FROM myAudit99
GO
UPDATE myTable99 SET Col2 = 'Z', ModifiedDate = GetDate()
GO
SELECT * FROM myTable99
SELECT * FROM myAudit99
GO
DELETE FROM myTable99 WHERE Col1 = 1
GO
SELECT * FROM myTable99
SELECT * FROM myAudit99
GO
SET NOCOUNT OFF
DROP TABLE myTable99
DROP TABLE myAudit99
GO
The problem arises in that I have an asset/inventory management app that dumps lots of details into my DB tables at once each time its run.
Not all of the tables are updated and data cannot be completely inserted.
This is the trigger i have been using - could someone tell me how to modify it to work.
/*
This trigger audit trails all changes made to a table.
It will place in the table Audit all inserted, deleted, changed columns in the table on which it is placed.
It will put out an error message if there is no primary key on the table
You will need to change @.TableName to match the table to be audit trailed
*/
ALTER trigger tr_TableName
on dbo.TableName for insert, update, delete
as
declare @.bit int ,
@.field int ,
@.maxfield int ,
@.char int ,
@.fieldname varchar(128) ,
@.TableName varchar(128) ,
@.PKCols varchar(1000) ,
@.sql varchar(2000),
@.UpdateDate varchar(21) ,
@.Action nvarchar(50) ,
@.HostName nvarchar(50),
@.PKFieldName varchar (1000)
IF EXISTS(SELECT * FROM inserted)
IF EXISTS(SELECT * FROM deleted)
--update = inserted and deleted tables both contain data
BEGIN
SET @.Action = 'UPDATE'
SELECT @.DeviceID = (SELECT inserted.DeviceID FROM inserted INNER JOIN deleted ON inserted.deviceID = deleted.deviceid)
END
ELSE
--insert = inserted contains data, deleted does not
BEGIN
SET @.Action = 'INSERT'
select @.DeviceID = (SELECT DeviceID from inserted)
END
ELSE
--delete = deleted contains data, inserted does not
BEGIN
SET @.Action = 'DELETE'
select @.DeviceID = (SELECT DeviceID from deleted)
END
select @.TableName = 'TableName'
-- date
select @.HostName = host_name(),
@.UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114),
--@.DeviceID,
@.PKFieldName=(select top 1 c.COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,INFORMATION_SCHEMA.KEY_COLUMN_USAGE c where pk.TABLE_NAME = @.TableName
and CONSTRAINT_TYPE = 'PRIMARY KEY' and c.TABLE_NAME = pk.TABLE_NAME and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME)
-- get list of columns
select * into #ins from inserted
select * into #del from deleted
-- Get primary key columns for full outer join
select @.PKCols = coalesce(@.PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @.TableName
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
if @.PKCols is null
begin
raiserror('no PK on table %s', 16, -1, @.TableName)
return
end
select @.field = 0, @.maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @.TableName
while @.field < @.maxfield
begin
select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @.TableName and ORDINAL_POSITION > @.field
select @.bit = (@.field - 1 )% 8 + 1
select @.bit = power(2,@.bit - 1)
select @.char = ((@.field - 1) / 8) + 1
--if substring(COLUMNS_UPDATED(),@.char, 1) & @.bit > 0
begin
select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @.TableName and ORDINAL_POSITION = @.field
select @.sql = 'insert LITE_Inventory (TableName, FieldName, OldValue, NewValue, UpdateDate, Action, Host, PkFieldName, DeviceID)'
-- select @.sql = 'insert LITE_Inventory (TableName, FieldName, OldValue, NewValue, UpdateDate, Action, Host, PkFieldName)'
select @.sql = @.sql + ' select ''' + @.TableName + ''''
select @.sql = @.sql + ',''' + @.fieldname + ''''
select @.sql = @.sql + ',convert(varchar(1000),d.' + @.fieldname + ')'
select @.sql = @.sql + ',convert(varchar(1000),i.' + @.fieldname + ')'
select @.sql = @.sql + ',''' + @.UpdateDate + ''''
select @.sql = @.sql + ',''' + @.Action + ''''
select @.sql = @.sql + ',''' + @.HostName + ''''
select @.sql = @.sql + ',''' + @.PKFieldName + ''''
select @.sql = @.sql + ' from #ins i full outer join #del d'
select @.sql = @.sql + @.PKCols
select @.sql = @.sql + ' where i.' + @.fieldname + ' <> d.' + @.fieldname
select @.sql = @.sql + ' or (i.' + @.fieldname + ' is null and d.' + @.fieldname + ' is not null)'
select @.sql = @.sql + ' or (i.' + @.fieldname + ' is not null and d.' + @.fieldname + ' is null)'
exec (@.sql)
end
endYou have to remember that inerted and deleted can have a set of data...not just 1 row...
Tracking chnages should br pretty straighty forward...
If the data is altered just move the whole row to history...|||You have to remember that inerted and deleted can have a set of data...not just 1 row...
Tracking chnages should br pretty straighty forward...
If the data is altered just move the whole row to history...
How do 'move the whole row to history' - im not sure.
The reason im using this trigger is that it automatically inserts data into a single audit table from all the tables where is has been applied. I can then show this audit table to my users though a GUI.|||USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE myTable99 (Col1 int IDENTITY(1,1), Col2 char(1), ModifiedDate datetime)
CREATE TABLE myAudit99 (AuditAddDate datetime DEFAULT GetDate(), Col1 int, Col2 char(1), ModifiedDate datetime)
GO
CREATE TRIGGER myTrigger99 ON myTable99
FOR UPDATE, DELETE
AS
INSERT INTO myAudit99(Col1, Col2, ModifiedDate)
SELECT Col1, Col2, ModifiedDate FROM deleted
GO
INSERT INTO myTable99(Col2)
SELECT 'x'
GO
SELECT * FROM myTable99
SELECT * FROM myAudit99
GO
UPDATE myTable99 SET Col2 = 'A', ModifiedDate = GetDate()
GO
SELECT * FROM myTable99
SELECT * FROM myAudit99
GO
UPDATE myTable99 SET Col2 = 'Z', ModifiedDate = GetDate()
GO
SELECT * FROM myTable99
SELECT * FROM myAudit99
GO
DELETE FROM myTable99 WHERE Col1 = 1
GO
SELECT * FROM myTable99
SELECT * FROM myAudit99
GO
SET NOCOUNT OFF
DROP TABLE myTable99
DROP TABLE myAudit99
GO
Audit Tables and triggers
Dear Group,
I would like to create an audit table that is created with a trigger that
reflects all the changes(insert, update and delete) that occur in table.
Say I have a table with
Subject_ID, visit_number, dob, weight, height, User_name, inputdate
The audit table would have .
Subject_ID, visit_number, dob, weight, height, User_name, inputdate,
edit_action, edit_reason.
Where the edit_action would be insert, update, delete; the edit_reason would
be the reason given for the edit.
Help with this would be great, since I am new to the world of triggers.
Thanks,
JeffJeff Magouirk (magouirkj@.njc.org) writes:
> I would like to create an audit table that is created with a trigger that
> reflects all the changes(insert, update and delete) that occur in table.
> Say I have a table with
> Subject_ID, visit_number, dob, weight, height, User_name,
> inputdate
> The audit table would have .
> Subject_ID, visit_number, dob, weight, height, User_name, inputdate,
> edit_action, edit_reason.
> Where the edit_action would be insert, update, delete; the edit_reason
> would be the reason given for the edit.
> Help with this would be great, since I am new to the world of triggers.
If you need to do to this on a broad scale, consider 3rd-party solutions.
Two that I usually recommend - although I've used none of them myself -
is SQLAudit from Red Matrix and Entegra from Lumigent. SQL Audit is
based on triggers, Entegra works from the transaction log.
But for a one-shot you could do:
CREATE TRIGGER tbl_audit_tri ON tbl FOR INSERT, UPDATE, DELETE
IF @.@.rowcount = 0
RETURN
IF EXISTS(SELECT * FROM inserted)
BEGIN
INSERT logtable (subject_id, ... edit_action)
SELECT subject_id, ...
CASE WHEN EXISTS (SELECT * FROM deleted)
THEN 'UPDATE'
ELSE 'INSERT'
FROM inserted
END
ELSE
BEGIN
INSERT logtable (subject_id, ... edit_action)
SELECT subject_id, ..., 'DELETE'
FROM delete
END
As you see I have left out edit_reason. This is because I don't know
what you mean with "edit_reason", and anyway it sounds like something
that can be quite difficult to get hold of from the trigger.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Wednesday, March 7, 2012
Audit one field in SQL Server 2005
Is it possible to use an update trigger to audit one field in a table? thanks
Hi Pete
An update trigger will fire no matter what columns are updated, but within
the trigger you can use a special construct IF UPDATE (column_name).
So you could check if one particular column was updated, and do what you
need to do, and otherwise, do nothing and return from the trigger.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"pete" <pete@.discussions.microsoft.com> wrote in message
news:5F5E90A7-415F-491A-9A95-BC8E19CAD72C@.microsoft.com...
> Is it possible to use an update trigger to audit one field in a table?
> thanks
Hi Pete
An update trigger will fire no matter what columns are updated, but within
the trigger you can use a special construct IF UPDATE (column_name).
So you could check if one particular column was updated, and do what you
need to do, and otherwise, do nothing and return from the trigger.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"pete" <pete@.discussions.microsoft.com> wrote in message
news:5F5E90A7-415F-491A-9A95-BC8E19CAD72C@.microsoft.com...
> Is it possible to use an update trigger to audit one field in a table?
> thanks
Audit one field in SQL Server 2005
Is it possible to use an update trigger to audit one field in a table? thanksHi Pete
An update trigger will fire no matter what columns are updated, but within
the trigger you can use a special construct IF UPDATE (column_name).
So you could check if one particular column was updated, and do what you
need to do, and otherwise, do nothing and return from the trigger.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"pete" <pete@.discussions.microsoft.com> wrote in message
news:5F5E90A7-415F-491A-9A95-BC8E19CAD72C@.microsoft.com...
> Is it possible to use an update trigger to audit one field in a table?
> thanks
An update trigger will fire no matter what columns are updated, but within
the trigger you can use a special construct IF UPDATE (column_name).
So you could check if one particular column was updated, and do what you
need to do, and otherwise, do nothing and return from the trigger.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"pete" <pete@.discussions.microsoft.com> wrote in message
news:5F5E90A7-415F-491A-9A95-BC8E19CAD72C@.microsoft.com...
> Is it possible to use an update trigger to audit one field in a table?
> thanks
Audit one field in SQL Server 2005
Is it possible to use an update trigger to audit one field in a table? than
ksHi Pete
An update trigger will fire no matter what columns are updated, but within
the trigger you can use a special construct IF UPDATE (column_name).
So you could check if one particular column was updated, and do what you
need to do, and otherwise, do nothing and return from the trigger.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"pete" <pete@.discussions.microsoft.com> wrote in message
news:5F5E90A7-415F-491A-9A95-BC8E19CAD72C@.microsoft.com...
> Is it possible to use an update trigger to audit one field in a table?
> thanks
ksHi Pete
An update trigger will fire no matter what columns are updated, but within
the trigger you can use a special construct IF UPDATE (column_name).
So you could check if one particular column was updated, and do what you
need to do, and otherwise, do nothing and return from the trigger.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"pete" <pete@.discussions.microsoft.com> wrote in message
news:5F5E90A7-415F-491A-9A95-BC8E19CAD72C@.microsoft.com...
> Is it possible to use an update trigger to audit one field in a table?
> thanks
Audit on 1 db
Hi guys
I need to give a contractor access to Audit/Trace 1 database. The contractor
also needs to be able to create, drop, insert, delete & update 1 database on
a server.
At the moment I have added him to the System Administrator role. Is there
any way I can restrict him to only the ONE database he actually needs to
access or does he need to System Admin role in order to Audit/trace.
Thanks.
Regards
JonasUsing profiler with SQL Server 2000 requires being a member
of sysadmin
If it's SQL Server 7, you can grant execute permissions on
the Profiler extended stored procedures - xp_trace_* and
xp_sqltrace.
-Sue
On Mon, 2 Aug 2004 11:17:09 +1000, "Jonas Larsen"
<Jonas.Larsen@.Alcan.com> wrote:
>Hi guys
>I need to give a contractor access to Audit/Trace 1 database. The contracto
r
>also needs to be able to create, drop, insert, delete & update 1 database o
n
>a server.
>At the moment I have added him to the System Administrator role. Is there
>any way I can restrict him to only the ONE database he actually needs to
>access or does he need to System Admin role in order to Audit/trace.
>Thanks.
>Regards
>Jonas
>
I need to give a contractor access to Audit/Trace 1 database. The contractor
also needs to be able to create, drop, insert, delete & update 1 database on
a server.
At the moment I have added him to the System Administrator role. Is there
any way I can restrict him to only the ONE database he actually needs to
access or does he need to System Admin role in order to Audit/trace.
Thanks.
Regards
JonasUsing profiler with SQL Server 2000 requires being a member
of sysadmin
If it's SQL Server 7, you can grant execute permissions on
the Profiler extended stored procedures - xp_trace_* and
xp_sqltrace.
-Sue
On Mon, 2 Aug 2004 11:17:09 +1000, "Jonas Larsen"
<Jonas.Larsen@.Alcan.com> wrote:
>Hi guys
>I need to give a contractor access to Audit/Trace 1 database. The contracto
r
>also needs to be able to create, drop, insert, delete & update 1 database o
n
>a server.
>At the moment I have added him to the System Administrator role. Is there
>any way I can restrict him to only the ONE database he actually needs to
>access or does he need to System Admin role in order to Audit/trace.
>Thanks.
>Regards
>Jonas
>
Saturday, February 25, 2012
Audit Design Question
I need to audit Insert, Update and Delete on tables in the database.
But the sy
min of the app can selectively enable and disable auditingon tables. So I need to be able to switch the auditing on and off.
Is there any built-in function in SqlServer 2005 that I can use to
track changes?
In the absence of built-in auditing I have come up with two possible
solutions:
1) Pass a patameter called @.bAudit to the stored procedures performing
CRUD tasks and if the parameter is true then insert a row into the
audit table (audit table has the same schema as the main table but with
a few more columns for tracking).
2) Use triggers on Insert, Update and Delete but I can't find out how
to disable triggers at run time.
Can you please point me in the right direction? Thanks.S Chapman,
> 2) Use triggers on Insert, Update and Delete but I can't find out how
> to disable triggers at run time.
Use the statement "alter table".
alter table dbo.t1
disable trigger trigger_name
See BOL for more info.
> 1) Pass a patameter called @.bAudit to the stored procedures performing
> CRUD tasks and if the parameter is true then insert a row into the
> audit table (audit table has the same schema as the main table but with
> a few more columns for tracking).
A possible solution could be having an options table and check the option
inside the trigger.
update conf_options
set audit = 1
where table_name = 'my_table'
go
create trigger tr_mytable on my_table
for insert, update, delete
as
if exists (select * from dbo.conf_options where table_name = 'my_table' and
audit = 1)
begin
-- put here the audit code
end
go
AMB
"S Chapman" wrote:
>
> I need to audit Insert, Update and Delete on tables in the database.
> But the sy
min of the app can selectively enable and disable auditing> on tables. So I need to be able to switch the auditing on and off.
> Is there any built-in function in SqlServer 2005 that I can use to
> track changes?
> In the absence of built-in auditing I have come up with two possible
> solutions:
> 1) Pass a patameter called @.bAudit to the stored procedures performing
> CRUD tasks and if the parameter is true then insert a row into the
> audit table (audit table has the same schema as the main table but with
> a few more columns for tracking).
> 2) Use triggers on Insert, Update and Delete but I can't find out how
> to disable triggers at run time.
> Can you please point me in the right direction? Thanks.
>
Subscribe to:
Posts (Atom)