In a table I have a column called ModifyDateTime which stores the last time
that record was modified. When a record is inserted this column is updated
through a default and when a record is updated a trigger is used. This is
all working fairly well except I would like the ModifyDateTime column to not
change if the data in the other columns is updated to the same thing.
Currently I need to make checks in my different place to ensure that an
update actually contains new data but if the trigger could do it then I'd
only need to check in one place.
Many thanks
Michael
eg
CREATE TABLE ABC(ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, SomeValue
NVARCHAR(5), ModifyDateTime DateTime NOT NULL DEFAULT GETDATE())
GO
CREATE TRIGGER [tgABC_Update] ON [dbo].[ABC]
FOR UPDATE
AS
UPDATE ABC SET ModifyDateTime = GETDATE() WHERE ID IN (SELECT ID FROM
INSERTED)
GO
INSERT INTO ABC (SomeValue) VALUES ('1')
SELECT * FROM ABC
--might need a delay here.
UPDATE ABC SET SomeValue = 1 --should not cause ModifyDateTime column to
change
SELECT * FROM ABC--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
IF Update(SomeValue)
RETURN
ELSE
UPDATE ABC SET ModifyDateTime = GETDATE() WHERE ID IN (SELECT ID FROM
inserted)
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQlDEIYechKqOuFEgEQKR0gCg2CL/V2dejzcsa164lHtJV8pWGssAniBt
RTNymjjW6r627hdwwiZQBamQ
=bfki
--END PGP SIGNATURE--
Michael C wrote:
> In a table I have a column called ModifyDateTime which stores the last tim
e
> that record was modified. When a record is inserted this column is updated
> through a default and when a record is updated a trigger is used. This is
> all working fairly well except I would like the ModifyDateTime column to n
ot
> change if the data in the other columns is updated to the same thing.
> Currently I need to make checks in my different place to ensure that an
> update actually contains new data but if the trigger could do it then I'd
> only need to check in one place.
> Many thanks
> Michael
> eg
> CREATE TABLE ABC(ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, SomeValue
> NVARCHAR(5), ModifyDateTime DateTime NOT NULL DEFAULT GETDATE())
> GO
> CREATE TRIGGER [tgABC_Update] ON [dbo].[ABC]
> FOR UPDATE
> AS
> UPDATE ABC SET ModifyDateTime = GETDATE() WHERE ID IN (SELECT ID FROM
> INSERTED)
> GO
> INSERT INTO ABC (SomeValue) VALUES ('1')
> SELECT * FROM ABC
> --might need a delay here.
> UPDATE ABC SET SomeValue = 1 --should not cause ModifyDateTime column to
> change
> SELECT * FROM ABC
>
In your trigger use the IF Update(column) construct. E.g.:|||Michael
Look at IF UPDATE(column name) command to be used inside the trigger.
"Michael C" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:e4$T%235MOFHA.3296@.TK2MSFTNGP15.phx.gbl...
> In a table I have a column called ModifyDateTime which stores the last
time
> that record was modified. When a record is inserted this column is updated
> through a default and when a record is updated a trigger is used. This is
> all working fairly well except I would like the ModifyDateTime column to
not
> change if the data in the other columns is updated to the same thing.
> Currently I need to make checks in my different place to ensure that an
> update actually contains new data but if the trigger could do it then I'd
> only need to check in one place.
> Many thanks
> Michael
> eg
> CREATE TABLE ABC(ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, SomeValue
> NVARCHAR(5), ModifyDateTime DateTime NOT NULL DEFAULT GETDATE())
> GO
> CREATE TRIGGER [tgABC_Update] ON [dbo].[ABC]
> FOR UPDATE
> AS
> UPDATE ABC SET ModifyDateTime = GETDATE() WHERE ID IN (SELECT ID FROM
> INSERTED)
> GO
> INSERT INTO ABC (SomeValue) VALUES ('1')
> SELECT * FROM ABC
> --might need a delay here.
> UPDATE ABC SET SomeValue = 1 --should not cause ModifyDateTime column to
> change
> SELECT * FROM ABC
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23c8snCNOFHA.3984@.TK2MSFTNGP12.phx.gbl...
> Michael
> Look at IF UPDATE(column name) command to be used inside the trigger.
Thanks Uri and MG,
IF Update(SomeValue)
RETURN
ELSE
UPDATE ABC SET ModifyDateTime = GETDATE() WHERE ID IN (SELECT ID FROM
inserted)
I'm presuming this won't work as I would like if the change was initiated
from an UPDATE statement and that causes only some of the data to change? eg
UPDATE ABC SET SomeValue = 'Z'
where half the rows already contain the value 'Z' in somefield.
I'm guessing the only method would be to use a cursor then to go through
each row. I can just use my existing method of checking everywhere I do an
update, which is probably the most efficient anyway because it never updates
the row at all.
Regards,
Michael|||Michael
Perhaps you need
UPDATE ABC SET SomeValue = 'Z' WHERE SomeValue <>'Z'
"Michael C" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:OeDmtqNOFHA.3336@.TK2MSFTNGP09.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23c8snCNOFHA.3984@.TK2MSFTNGP12.phx.gbl...
> Thanks Uri and MG,
> IF Update(SomeValue)
> RETURN
> ELSE
> UPDATE ABC SET ModifyDateTime = GETDATE() WHERE ID IN (SELECT ID FROM
> inserted)
> I'm presuming this won't work as I would like if the change was initiated
> from an UPDATE statement and that causes only some of the data to change?
eg
> UPDATE ABC SET SomeValue = 'Z'
> where half the rows already contain the value 'Z' in somefield.
> I'm guessing the only method would be to use a cursor then to go through
> each row. I can just use my existing method of checking everywhere I do an
> update, which is probably the most efficient anyway because it never
updates
> the row at all.
> Regards,
> Michael
>|||On Mon, 4 Apr 2005 15:59:34 +1000, Michael C wrote:
>"Uri Dimant" <urid@.iscar.co.il> wrote in message
>news:%23c8snCNOFHA.3984@.TK2MSFTNGP12.phx.gbl...
>Thanks Uri and MG,
>IF Update(SomeValue)
> RETURN
>ELSE
> UPDATE ABC SET ModifyDateTime = GETDATE() WHERE ID IN (SELECT ID FROM
>inserted)
>I'm presuming this won't work as I would like if the change was initiated
>from an UPDATE statement and that causes only some of the data to change? e
g
>UPDATE ABC SET SomeValue = 'Z'
>where half the rows already contain the value 'Z' in somefield.
>I'm guessing the only method would be to use a cursor then to go through
>each row. I can just use my existing method of checking everywhere I do an
>update, which is probably the most efficient anyway because it never update
s
>the row at all.
Hi Michael,
Oh no, there absolutely no need to slow things down by using a cursor;
this can easily be accomplished with setbased instructions:
IF UPDATE(SomeColumn) -- Saves time if the colummn's not updated at all
UPDATE ABC
SET ModifyDateTime = CURRRENT_TIMESTAMP
WHERE EXISTS
(SELECT *
FROM deleted AS d -- Holds old vales (before update)
WHERE d.KeyCol1 = ABC.KeyCol1 -- Repeat for all columns that
AND d.KeyCol2 = ABD.KeyCol2 -- make up the primary key
AND d.SomeColumn <> ABC.SomeColumn) -- Actually changed?
The above assumes that SomeColumn will never be NULL. If it can be NULL,
you'll have to change the last line. Either like this, using a value
that will never be really used in the column:
AND COALESCE(d.SomeColumn, -123)
<> COALESCE(ABC.SomeColumn, -123)
Or, if you don't have any value that will never be in the column (or
prefer not to rely on such assumptions), you can use this not very
intuuitive but very reliable version:
AND NULLIF(d.SomeColumn, ABC.SomeColumn) IS NULL
AND NULLIF(ABC.SomeColumn, d.SomeColumn) IS NULL
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uZAnIEOOFHA.2936@.TK2MSFTNGP10.phx.gbl...
> Michael
> Perhaps you need
> UPDATE ABC SET SomeValue = 'Z' WHERE SomeValue <>'Z'
Thanks Uri. Unfortunately, that is what I'm trying to avoid. I have a few
statements like what you've written here that update the same table but they
are much more complicated, if the trigger does the checking then I will only
need the check in one central location. In the end it is not really that
important and I might do it either way, or even both for efficiency (as your
statement updates less records).
Regards,
Michael|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:3n5351pi5ggb1dov6qqej33k9dq9esnvnm@.
4ax.com...
> On Mon, 4 Apr 2005 15:59:34 +1000, Michael C wrote:
Thank Hugo, I'll have a look at it now. I really like the NULLIF idea, i've
been using
ISNULL(X, '{00000000-0000-0000-0000-00000000}') = ISNULL(Y,
'{00000000-0000-0000-0000-00000000}')
for guids, which I've always thought could be simpler.
Michael|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:3n5351pi5ggb1dov6qqej33k9dq9esnvnm@.
4ax.com...
> Oh no, there absolutely no need to slow things down by using a cursor;
> this can easily be accomplished with setbased instructions:
Thanks Hugo, that worked perfectly after lots of playing around. I had a
couple of questions if you don't mind. Is it preferrable to use
CURRENT_TIMESTAMP instead of GETDATE()? Ditto for EXISTS instead of an IN
clause?
> AND NULLIF(d.SomeColumn, ABC.SomeColumn) IS NULL
> AND NULLIF(ABC.SomeColumn, d.SomeColumn) IS NULL
Is this something they overlooked in SQLServer? Seems to me there should
something built in for comparing 2 values that you want to consider equal
when both are null.
Thanks,
Michael|||On Tue, 5 Apr 2005 14:20:32 +1000, Michael C wrote:
(snip)
> I had a
>couple of questions if you don't mind.
Hi Michael,
I don't mind :-)
> Is it preferrable to use
>CURRENT_TIMESTAMP instead of GETDATE()?
GETDATE() is the name chosen for the function in Sybase and SQL Server
before there were any standards to adhere to. The ANSI standard settled
for CURRENT_TIMESTAMP. Since either 7.0 or 2000, both are recognised by
SQL Server. Many people still use GETDATE (I do - old habits die hard),
but for portability, CURRENT_TIMESTAMP is prefered.
> Ditto for EXISTS instead of an IN
>clause?
I prefer to use IN only with a list of constants (as a shorthand for a
range of expressions OR'ed together), and EXISTS in all other cases. Not
for portability (both conform to ANSI standard), not for performance
(though I've read reports claiming that EXISTS is often faster), but for
another reason.
If you combine the IN operator with NOT (i.e. NOT IN), and the subquery
after NOT IN returns one or more NULL values, then the NOT IN operator
will never evaluate to true. This surprises lots of people, because many
people have trouble understanding the role NULL plays in the relational
model. What would you answer if I asked you if the number 8 is IN
(current monthnumber, age of my daughter, #days per w
)?
>Is this something they overlooked in SQLServer? Seems to me there should
>something built in for comparing 2 values that you want to consider equal
>when both are null.
This is not an oversight, it's a feature (and no, this is not intended
as a cynical remark, I'm serious). NULL means that the data is missing,
not available, unknown. I don't think that you should ever want a
database to conclude that two unknown values are equal. Is my daughter's
age equal to the age of the girl living next door to me?
And even if it is an oversight, then definitely not in SQL Server. In
this regard, SQL Server strictly adheres to the ANSI standard handling
of NULL.
Besides, there IS a way to compare 2 values and consider them equal of
both are NULL. Several ways, in fact. You can use a combination of two
NULLIF expressions like the one above. Or you can use COALESCE to
replace NULL with a value that won't ever appear in regular data, then
compare the resutls. Or you could use an expression such as "col1 = col2
OR (col1 IS NULL AND col2 IS NULL)"
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
