Trigger update help 1

[EchoAlertcom]

Hello,

I need some simple help please.

I need a trigger that will update the OpenedDate column when the Opened INT field is updated on a specific row. I'm affraid what I have below will update the OpenedDate column for ALL the records not just the record that the Opened INT was updated.

I would appreciate any suggestions.

Thanks,
Steve

CREATE TRIGGER [trgOpenDate]
ON [dbo].[Addresses]
FOR UPDATE
AS
IF UPDATE(Opened)
Begin
UPDATE [dbo].[addresses] SET OpenedDate = GetDate()

 

[SQLDenis]

you will have to join addresses with the deleted table
something like this

CREATE TRIGGER [trgOpenDate]
ON [dbo].[Addresses]
FOR UPDATE
AS
IF UPDATE(Opened)
Begin
update a SET a.OpenedDate = GetDate()
from [dbo].[addresses] a join deleted d on a.KeyField =d.Keyfield


and keyfield would be you key or id that would be unique



“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[EchoAlertcom]

Is the deleted table a table i would have created or is that naturally there?

 

[SQLDenis]

Deleted is available in an update trigger and a deleted trigger
and update trigger will also have an inserted table, basically the deleted table is the old row and the inserted table is the new row

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[EchoAlertcom]

Thanks. I am getting an error when I check syntax

"incorrect syntax near 'UID' I narrowed it down to the UID of the deleted table by adding a letter and watching the error message change.

UID is my unique key in dbo.addresses

CREATE TRIGGER [trgOpenDate]
ON [dbo].[Addresses]
FOR UPDATE
AS
IF UPDATE(Opened)
Begin
update a SET a.OpenedDate = GetDate()
from [dbo].[addresses] a join deleted d on a.UID = d.UID

Any suggestions?

 

[SQLDenis]

you are missing the end statement

CREATE TRIGGER [trgOpenDate]
ON [dbo].[Addresses]
FOR UPDATE
AS
IF UPDATE(Opened)
Begin
update a SET a.OpenedDate = GetDate()
from [dbo].[addresses] a join deleted d on a.UID = d.UID
end

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[EchoAlertcom]

woops. Thanks.

That worked great, thanks for the help.

Regards,
Steve

 

[druer]

I've read something lately that may be a caution to you on the IF UPDATE, others can correct me if I'm wrong which will also help me as the alternative is much more costly.

The issue I've read is that the IF UPDATE command returns true if the field value is on the left side of an assignment statement, which does not necessarily imply that the value is actually updated. Meaning that in our environment the developers issue a mass update statement that includes all of the fields on the form. So all fields would return TRUE if the IF UPDATE were used. Suggestions I've found are to compare the values of the fields from the INSERTED and DELETED tables to gain an accurate reflection of whether the field actually changed or not, ie:

where inserted.opened <> deleted.opened

As I say I may have been missing something in what I read, that I'm sure others will respond to, but if it is true you may want to consider that and what the impacts will be.