Billy Pang
tuxedo_man at hotmail.com
Fri Nov 7 14:46:56 CST 2003
oooopps.. sorry, I meant to type the "UPDATE" function
>From BOL:
"IF UPDATE (column)
Tests for an INSERT or UPDATE action to a specified column and is not used
with DELETE operations. More than one column can be specified. Because the
table name is specified in the ON clause, do not include the table name
before the column name in an IF UPDATE clause. To test for an INSERT or
UPDATE action for more than one column, specify a separate UPDATE(column)
clause following the first one. IF UPDATE will return the TRUE value in
INSERT actions because the columns have either explicit values or implicit
(NULL) values inserted."
Below is code the demonstrates how it works..
Use UPDATE function that will determine if the columns you are monitoring
for changes for have changed. If no change, then don't do anything. If
there is something, then you can catch for that.
HTH Billy
-- cut here --
SET NOCOUNT ON
GO
USE TEMPDB
GO
-- Create the THE_TABLE table
CREATE TABLE the_table(
THE_ID INT NOT NULL DEFAULT(0) PRIMARY KEY,
THE_DATE_A SMALLDATETIME NOT NULL DEFAULT('1/1/2003'),
THE_DATE_B SMALLDATETIME NOT NULL DEFAULT('1/1/2003'),
THE_COMMENT VARCHAR(100));
go
-- Create an after udapte trigger for THE_TABLE that will monitor changes to
the THE_DATE_A field
-- and if that column is updated, then do something like update the
THE_COMMENT field.
-- Therefore, if another column was updated, then don't do anything
CREATE TRIGGER TR_THE_TABLE ON THE_TABLE AFTER UPDATE AS
BEGIN
IF UPDATE(THE_DATE_A)
BEGIN
-- do something only if the THE_DATE_A column is updated
UPDATE THE_TABLE SET THE_COMMENT = 'trigger was here' FROM THE_TABLE X
INNER JOIN INSERTED Y ON X.THE_ID = Y.THE_ID;
END
END
GO
INSERT INTO THE_TABLE(THE_ID) VALUES(101);
INSERT INTO THE_TABLE(THE_ID) VALUES(102);
INSERT INTO THE_TABLE(THE_ID) VALUES(103);
GO
SELECT * FROM THE_TABLE;
/* results look like this
THE_ID THE_DATE_A THE_DATE_B THE_COMMENT
101 2003-01-01 00:00:00 2003-01-01 00:00:00 NULL
102 2003-01-01 00:00:00 2003-01-01 00:00:00 NULL
103 2003-01-01 00:00:00 2003-01-01 00:00:00 NULL
*/
GO
UPDATE THE_TABLE SET THE_DATE_A = '1/1/2003' WHERE THE_ID = 101;
UPDATE THE_TABLE SET THE_DATE_B = '1/2/2003' WHERE THE_ID = 102;
UPDATE THE_TABLE SET THE_DATE_A = '1/3/2003' WHERE THE_ID = 103;
GO
-- Notice that even though all three records have been modified, only two of
them have values in THE_COMMENT field.
SELECT * FROM THE_TABLE;
/*
THE_ID THE_DATE_A THE_DATE_B THE_COMMENT
101 2003-01-01 00:00:00 2003-01-01 00:00:00 trigger was here
102 2003-01-01 00:00:00 2003-01-02 00:00:00 NULL
103 2003-01-03 00:00:00 2003-01-01 00:00:00 trigger was here
*/
GO
GO
-- Drop the THE_TABLE table
DROP TABLE THE_TABLE;
GO
SET NOCOUNT OFF
-- cut here --
>From: "Martin Reid" <mwp.reid at qub.ac.uk>
>Reply-To: dba-sqlserver at databaseadvisors.com
>To: <dba-sqlserver at databaseadvisors.com>
>Subject: Re: [dba-SQLServer]Tracking updates
>Date: Fri, 7 Nov 2003 11:58:47 -0800
>
>Billy
>
>I can find no reference to an UPDATED() function???
>
>Martin
>
>
>
>----- Original Message -----
>From: "Billy Pang" <tuxedo_man at hotmail.com>
>To: <dba-sqlserver at databaseadvisors.com>
>Sent: Friday, November 07, 2003 11:31 AM
>Subject: Re: [dba-SQLServer]Tracking updates
>
>
> > Create an AFTERUPDATE trigger on that table that checks for updated
>columns
> > using the UPDATED() function.
> >
> > Billy
> >
> >
> > >From: "Martin Reid" <mwp.reid at qub.ac.uk>
> > >Reply-To: dba-sqlserver at databaseadvisors.com
> > >To: <dba-sqlserver at databaseadvisors.com>
> > >Subject: [dba-SQLServer]Tracking updates
> > >Date: Fri, 7 Nov 2003 08:18:42 -0800
> > >
> > >Is there a simple way to catch when a table was updated?
> > >
> > >I have a table in SQL Server, same table in a scanner (more or less)
> > >
> > >I dont want to download or upload if there have been no changes to the
> > >table.
> > >
> > >Or is there another approach to this?
> > >
> > >Martin
> > >
> > >_______________________________________________
> > >dba-SQLServer mailing list
> > >dba-SQLServer at databaseadvisors.com
> > >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > >http://www.databaseadvisors.com
> > >
> >
> > _________________________________________________________________
> > Protect your PC - get McAfee.com VirusScan Online
> > http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
> >
> > _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> >
> >
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
_________________________________________________________________
The new MSN 8: smart spam protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail
http://join.msn.com/?page=dept/bcomm&pgmarket=en-ca&RU=http%3a%2f%2fjoin.msn.com%2f%3fpage%3dmisc%2fspecialoffers%26pgmarket%3den-ca