All about Triggers...

Created my first trigger a day or two ago that inserts the current date
into a smalldatetime field WHEN another field is updated. My question:
why is it that one cannot update that field a second time using my
website front end? It's as if you enter in that field's info once and
then you're stuck with it... :/ All the other fields may be modified
at will except that one.
My trigger:
CREATE TRIGGER set_date_trig
ON lla
FOR UPDATE
AS
SET NOCOUNT ON
BEGIN
IF UPDATE(test)
UPDATE lla
SET test_date=GETDATE()
WHERE doc+poe IN (SELECT doc+poe FROM inserted)
END
Any ideas?Try,
...
UPDATE
a
SET
a.test_date=GETDATE()
from
lla as a
inner join
inserted as i
on a.doc = i.doc and a.poe = i.poe
AMB
"roy.anderson@.gmail.com" wrote:

> Created my first trigger a day or two ago that inserts the current date
> into a smalldatetime field WHEN another field is updated. My question:
> why is it that one cannot update that field a second time using my
> website front end? It's as if you enter in that field's info once and
> then you're stuck with it... :/ All the other fields may be modified
> at will except that one.
> My trigger:
> CREATE TRIGGER set_date_trig
> ON lla
> FOR UPDATE
> AS
> SET NOCOUNT ON
> BEGIN
> IF UPDATE(test)
> UPDATE lla
> SET test_date=GETDATE()
> WHERE doc+poe IN (SELECT doc+poe FROM inserted)
> END
>
> Any ideas?
>|||Since this is an UPDATE trigger any change you make to the column Test will
also overwrite Test_date. Maybe you just wanted a DEFAULT value for the
column? Example:
ALTER TABLE lla
ADD CONSTRAINT df_lla_test_date
DEFAULT CURRENT_TIMESTAMP FOR test_date
In your UPDATE:
...
WHERE doc+poe IN
This seems unlikely to be the correct or best way to correlate with the
Inserted table. Is (Doc,Poe) the key? If so:
UPDATE lla SET test_date = CURRENT_TIMESTAMP
WHERE EXISTS
(SELECT *
FROM Inserted AS I
WHERE I.doc = lla.doc
AND I.poe = lla.poe)
Concatenating (or adding!?) the two columns would otherwise not give your
query the full benefit of an index on these columns. Concatenating VARCHAR
columns in this way could also give you incorrect results.
David Portas
SQL Server MVP
--|||The UPDATE() function checks to see if the column is in the update
statement. It does NOT check to see if the value is changing from the
original value. I assume when you try your update statement from the
front-end to set the Test_Date to something expilicit it still sets it to
GetDate(). You may need something like this for your trigger. I'm assuming
your primary key is a combination of Doc and Poe...
CREATE TRIGGER set_date_trig
ON lla
FOR UPDATE
AS
SET NOCOUNT ON
BEGIN
UPDATE lla SET
lla.test_date = GETDATE()
FROM lla
INNER JOIN Inserted I ON lla.Doc = I.Doc AND lla.Poe = I.Poe
INNER JOIN Deleted D ON I.Doc = D.Doc AND I.Poe = D.Poe
WHERE I.Test <> D.Test
END
Paul
"roy.anderson@.gmail.com" wrote:

> Created my first trigger a day or two ago that inserts the current date
> into a smalldatetime field WHEN another field is updated. My question:
> why is it that one cannot update that field a second time using my
> website front end? It's as if you enter in that field's info once and
> then you're stuck with it... :/ All the other fields may be modified
> at will except that one.
> My trigger:
> CREATE TRIGGER set_date_trig
> ON lla
> FOR UPDATE
> AS
> SET NOCOUNT ON
> BEGIN
> IF UPDATE(test)
> UPDATE lla
> SET test_date=GETDATE()
> WHERE doc+poe IN (SELECT doc+poe FROM inserted)
> END
>
> Any ideas?
>