To limit a trigger action to one row, add the following immediately =
after the AS:
IF @@ROWCOUNT =3D 0 -- Nothing to do, leave
RETURN
IF @@ROWCOUNT > 1 -- Too many rows, undo them all
BEGIN
ROLLBACK
RETURN
END
I would tend to separate UPDATE and DELETE actions into independent =
triggers.
For the issue of actions to take based upon columns updated, read up on =
UPDATED() or UPDATING() in Books Online.=20
You can 'direct traffic' by using:
IF UPDATE(ColumnName)
BEGIN
{actions to take, including inserts to another table}
END
UPDATING() does the same thing, but allows multiple columns (in the form =
of bitmaps) to be checked.
By the way, a TIMESTAMP datatype is not a datetime value, but is a =
sequential integer that is changed every time ANY column on the row is =
changed. I suspect you meant to indicate that you have a datetime field =
that needs to be changed to the last modification date/time. Using =
[TIMESTAMP] as a column name is confusing, and 'should' be avoided. Why =
not call it?'LastCriticalFieldUpdate'?
--=20
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.=20
Most experience comes from bad judgment.=20
- Anonymous
[quoted text, click to view] <ryan332211@hotmail.com> wrote in message =
news:1159228843.720324.167440@m7g2000cwm.googlegroups.com...
>I have a database with 300K rows, each row has 80 columns. I would
> like to:
>=20
> 1. update one of the columns "LastUpdateColumn" whenever one of 10
> specific columns have changed. This column would have a timestamp
> field. This will give me a representation of the last time one of the
> 10 critical fields has been updated.
> 2. I would also insert a row into another table (an audit table).
>=20
> I would ignore if one of the the other 70 columns have changed value,
> only care if one of the 10 specific columns have changed value.
>=20
> I have never done a trigger before and have not been able to find a
> sample that would show something like this.
>=20
> Also, i am interested in building a trigger on the DELETE and UPDATE
> statement that would roll back the transaction if the DELETE statement
> deletes more than one row or if the UPDATE statement updates more than
> an arbitrary number of rows (maybe 1, maybe 10, it is something that i
> will hard code, but am not certain of the number right now).
>=20
> I have found samples of triggers when a delete statement is executed,
> but how do i (1) test the number of rows that are desired to be
> deleted, and (2) if it is more than one, how do i roll back the entire
> transaction gracefully so that the client gets a result "0 rows
> deleted" ?
>=20
> Any help is greatly appreciated. Thanks
[quoted text, click to view] On Mon, 25 Sep 2006 17:45:47 -0700, Arnie Rowland wrote:
>To limit a trigger action to one row, add the following immediately after the AS:
>
>IF @@ROWCOUNT = 0 -- Nothing to do, leave
> RETURN
>
>IF @@ROWCOUNT > 1 -- Too many rows, undo them all
> BEGIN
> ROLLBACK
> RETURN
> END
Hi Arnie,
Not good - the first IF will reset @@ROWCOUNT before the second IF
starts.
DECLARE @rc int
SET @rc = @@ROWCOUNT
IF @@rc = 0 -- Nothing to do, leave
RETURN
IF @@rc > 1 -- Too many rows, undo them all
BEGIN
ROLLBACK
RETURN
END
[quoted text, click to view] >I would tend to separate UPDATE and DELETE actions into independent triggers.
Agreed.
[quoted text, click to view] >For the issue of actions to take based upon columns updated, read up on UPDATED() or UPDATING() in Books Online.
>You can 'direct traffic' by using:
>
>IF UPDATE(ColumnName)
> BEGIN
> {actions to take, including inserts to another table}
> END
Beware that this only tells if the column was present in the SET clause
of the UPDATE statement, not whether the new data is actuallly different
from the original data.
[quoted text, click to view] >UPDATING() does the same thing, but allows multiple columns (in the form of bitmaps) to be checked.
I presume you meant COLUMNS_UPDATED(). Don't use it - it's ugly,
impossible to understand, even harder to maintain, plus it introduces an
undocumented and unnecessary dependency on column order.
--
