update date using trigger?

kes,

Updating the table from within the trigger will not refire the trigger, so
you will not get into a infinite loop.

However, if your updates are coming through stored procedures or other
(perhaps C#) code that is managed by you, then updating the date without the
trigger is faster than using a trigger.

One biggish system on SQL Server 2000 tracked about 70% of the execution
time for their simple updates actually went to the trigger that was updating
the current time and the current user name. But, SQL Server 2005 has
changed the implementation of the inserted and deleted tables and it is
faster, but not free.

RLF

"WebBuilder451" wrote in message
news:A3B32CAC-B5E5-4384-BA1E-B9C0ACCE2F8E@microsoft.com...
>I have a last updated column in a table and i want the date to be set to
>the
> current date on update. I was hoping to use an update trigger on the
> table,
> but thought of a logic issue. If i set it to update on up date i've got a
> loop.
> How is this generaly done?
> --
> (i''ll be asking a lot of these, but I find C# totally way cooler than vb
> and there''s no go''n back!!!)
> thanks (as always)
>
> kes

On Fri, 28 Dec 2007 16:37:53 -0500, Russell Fields wrote:

>kes,
>
>Updating the table from within the trigger will not refire the trigger, so
>you will not get into a infinite loop.

Hi Russell,

That's default behaviour, but this can be changed by setting the
RECURSIVE_TRIGGERS database option!

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

On Fri, 28 Dec 2007 13:21:01 -0800, WebBuilder451 wrote:

>I have a last updated column in a table and i want the date to be set to the
>current date on update. I was hoping to use an update trigger on the table,
>but thought of a logic issue. If i set it to update on up date i've got a
>loop.
>How is this generaly done?

Hi Kes,

See my reply to Russell. If you want to make sure that you don't start
an infinite loop even when recursive triggers are enabled, you can use
several other options:

* Check IF UPDATE(column_name) to see which columns are affected. If the
LastUpdated column was updated, it was probably fired by the trigger. If
no other columns were updated, the likelyhood increases.

* Check @@NESTLEVEL to see how deep the nesting is. This is not a really
safe method, since your code might have procedures calling other
procedures, making it hard to identity the nest level where you want to
stop trigger execution.

* Check TRIGGER_NESTLEVEL(..) with the object id of the trigger. If this
is 2 or more, the trigger is fired from within itself (or from within
another trigger fired from the first). This is usually the best way,
except when you have many triggers that update other tables (and hence
cause other triggers to fire).

* Create a special table that you load with a specific value when
updating LastUpdated and check for this value on the start of the
trigger. Beware that this method, depending on the exact way you
implement it, will either break down uinder concurrent load or cause
contingency when concurrency increases.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

>> I have a last updated column in a table and I want the date to be set to the current date on update. <<

If this is for auditing purposes, then you don't do it in the table at
all. You catch it in the log file with an external tool.

>> How is this generally done? <<

First, put DEFAULT CURRENT_TIMESTAMP clause on the column so that it
is set at insertion time. After that, most of us will have a "SET
change_date = CURRENT_TIMESTAMP" clause in the stored procedure we are
using to handle the updates -- I assume you will be doing some other
validations when you update a table and not blindly accepting
anything. The change_date will not be one of the parameters you pass
the procedure.

SQL is declarative and you are still stuck in a VB/C# procedural
mindset. Make the engine do the work for you.

On Fri, 28 Dec 2007 14:54:19 -0800 (PST), --CELKO-- wrote:

>>> I have a last updated column in a table and I want the date to be set to the current date on update. <<
>
>If this is for auditing purposes, then you don't do it in the table at
>all. You catch it in the log file with an external tool.
>
>>> How is this generally done? <<
>
>First, put DEFAULT CURRENT_TIMESTAMP clause on the column so that it
>is set at insertion time. After that, most of us will have a "SET
>change_date = CURRENT_TIMESTAMP" clause in the stored procedure we are
>using to handle the updates -- I assume you will be doing some other
>validations when you update a table and not blindly accepting
>anything. The change_date will not be one of the parameters you pass
>the procedure.
>
>SQL is declarative and you are still stuck in a VB/C# procedural
>mindset. Make the engine do the work for you.

Hi Joe,

Setting the audit date in the stored procedure has one big disadvantage.
If data is, somehow, screwed up and a DBA is called in to do emergency
repairs, he may have to modify the table data directly. Now all updates
are audited - except the very type of update that auditors are most
eager to catch!

Using a trigger to catch updates and set the date will always work, no
matter how the data is changed. Much safer.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Hugo Kornelis (hugo@perFact.REMOVETHIS.info.INVALID) writes:
> Setting the audit date in the stored procedure has one big disadvantage.
> If data is, somehow, screwed up and a DBA is called in to do emergency
> repairs, he may have to modify the table data directly. Now all updates
> are audited - except the very type of update that auditors are most
> eager to catch!
>
> Using a trigger to catch updates and set the date will always work, no
> matter how the data is changed. Much safer.

Unless some disables the trigger of course!

But it's true that this requires outright malicious tampering, whereas
relying a on stored procedure will mean that manual meddling directly from
SQL will not be tracked.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

> Setting the audit date in the stored procedure has one big disadvantage.
> If data is, somehow, screwed up and a DBA is called in to do emergency
> repairs, he may have to modify the table data directly. Now all updates
> are audited - except the very type of update that auditors are most
> eager to catch!
>
> Using a trigger to catch updates and set the date will always work, no
> matter how the data is changed. Much safer.

If the auditors are trying to catch DBAs updating the data manually, and the
DBA knows enough about this, then he/she could always update that column
directly, after updating the data in the row. (Assuming the trigger does
not fire using IF UPDATE(LastUpdated).)

A

Actually Celko's statement is both correct and applicable (for once). True
auditing should be done via transaction log interpretation. Logging cannot
be disabled or bypassed like sprocs, triggers, etc.

Now, where ever the audit data is stored is succeptible to modification by
at least one person however. Thus my belief that auditing is never truly
trustworthy. :-)

--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net


"Hugo Kornelis" wrote in message
news:n30bn3136ra8qsjbr9ok5rekg80e8rdmoa@4ax.com...
> On Fri, 28 Dec 2007 14:54:19 -0800 (PST), --CELKO-- wrote:
>
>>>> I have a last updated column in a table and I want the date to be set
>>>> to the current date on update. <<
>>
>>If this is for auditing purposes, then you don't do it in the table at
>>all. You catch it in the log file with an external tool.
>>
>>>> How is this generally done? <<
>>
>>First, put DEFAULT CURRENT_TIMESTAMP clause on the column so that it
>>is set at insertion time. After that, most of us will have a "SET
>>change_date = CURRENT_TIMESTAMP" clause in the stored procedure we are
>>using to handle the updates -- I assume you will be doing some other
>>validations when you update a table and not blindly accepting
>>anything. The change_date will not be one of the parameters you pass
>>the procedure.
>>
>>SQL is declarative and you are still stuck in a VB/C# procedural
>>mindset. Make the engine do the work for you.
>
> Hi Joe,
>
> Setting the audit date in the stored procedure has one big disadvantage.
> If data is, somehow, screwed up and a DBA is called in to do emergency
> repairs, he may have to modify the table data directly. Now all updates
> are audited - except the very type of update that auditors are most
> eager to catch!
>
> Using a trigger to catch updates and set the date will always work, no
> matter how the data is changed. Much safer.
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

TheSQLGuru (kgboles@earthlink.net) writes:
> Actually Celko's statement is both correct and applicable (for once).
> True auditing should be done via transaction log interpretation.
> Logging cannot be disabled or bypassed like sprocs, triggers, etc.

"I had to truncate the transaction log, because it was filling up the
disk".

But I agree that the transaction is a much better space for auditing.
While the DBA can fiddle with the log, the missing log chain will not
go unnoticed. (Well, the DBA could manipulate the log directly, but
that would require very high skills and knowledge about the t-log format
which is not common knowledge.)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx