Trigger after table update.. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Trigger after table update..

Author

Topic

BigDelboy
Starting Member

3 Posts

Posted - 2010-11-22 : 12:14:17

Hi all

I new here and sorry if I am posting into the wrong section..

I wish to create a trigger on a table which can identify when a row is updated and then update a date field within that row displaying the date it was last updated.

I would be happy if someone could point out somewhere in you vast knowledge base here where I can get my answer..

Kind regards,

D.

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-22 : 12:45:56

[code]

CREATE TRIGGER [dbo].[myTrigger99] ON [dbo].[myTable99] FOR UPDATE
AS
SET NOCOUNT ON

IF EXISTS (Select * From Inserted) And Exists (Select * From Deleted)
UPDATE myTable99 SET UPD_DT = GetDate()
GO

[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-22 : 13:53:12

Brett - are you just kidding?
Create Trigger for Update --> no need to check inserted and deleted.
And your update statement is updating every row in the table without any relation to inserted/delted

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-22 : 14:05:31

quote:
Originally posted by BigDelboy

Hi all

I new here and sorry if I am posting into the wrong section..

I wish to create a trigger on a table which can identify when a row is updated and then update a date field within that row displaying the date it was last updated.

I would be happy if someone could point out somewhere in you vast knowledge base here where I can get my answer..

Kind regards,

D.

We need the table structure to give a solution.

No, you're never too old to Yak'n'Roll if you're too young to die.

BigDelboy
Starting Member

3 Posts

Posted - 2010-11-23 : 04:26:52

Hi Guys.. thanks for the prompt attention..

In my table I have a GUID field and have to update a DateTime field when the record gets updateded externally. example a record is added to the table at some point..

GUID - Address - Postcode - CreateDate - UpdatedDate
1234ETC - 12 Dane Rd - SW30 8ET - 01/01/2010ETC - NULL

Once the record is updated, I want the trigger to update the UpdatedDate field..

GUID - Address - Postcode - CreateDate - UpdatedDate
1234ETC - 13 Dane Rd - SW30 8ET - 01/01/2010ETC - 23/11/2010ETC

I hope this gives you the info you require..

Kind regards and many thanks,

Derek.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-23 : 04:35:23

create trigger tr_tbl on tbl for update
as
update tbl
set UpdatedDate = getdate()
from tbl t
join inserted i
on t.guid = i.guid
go

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

BigDelboy
Starting Member

3 Posts

Posted - 2010-11-23 : 05:45:34

Perfect Nigel..

Many thanks for your reply today, it helped and solved my problem..

I can go on and cause more problems with this knowledge now :)

Kind regards

Derek.

s3245863
Starting Member

2 Posts

Posted - 2010-12-13 : 09:39:58

Hi! I'm pretty new to Trigger and I have a problem here
I want to update a table and use a trigger to check a condition for the unique constraint. For example, the username. Here is my code but it seems not to work correctly:

USE [TMSDB]
GO
/****** Object: Trigger [dbo].[TR_UPDATE_USER] Script Date: 12/13/2010 20:56:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TR_UPDATE_USER] ON [dbo].[User]
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON
-- Check for duplicate User. If there is no duplicate, do an update.
IF (NOT EXISTS (SELECT U.Userid
FROM [User] U, inserted I
WHERE U.Username = I.Username))
UPDATE [User]
SET [Username] = I.Username,
[Password] = I.Password,
[Firstname] = I.Firstname,
[Middlename] = I.Middlename,
[Lastname] = I.Lastname,
[Email] = I.Email,
[ManagerID] = I.ManagerID
FROM [User] U, inserted I
WHERE U.Userid = I.Userid
END

Can anyone tell me what wrong with this ?

Tuan

X002548
Not Just a Number

15586 Posts

Posted - 2010-12-13 : 12:32:57

That was a good lunch


 CREATE TRIGGER [dbo].[myTrigger99] ON [dbo].[myTable99] FOR UPDATE
AS 
	SET NOCOUNT ON 
 
--	IF EXISTS (Select * From Inserted) And Exists (Select * From Deleted)
		UPDATE m SET UPD_DT = GetDate()
		  FROM myTable99 m INNER JOIN ON inserted i on i.key = m.key
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

s3245863
Starting Member

2 Posts

Posted - 2010-12-13 : 20:33:00

Thanks X002548!
It works now but I still don't get it. I know that The 'inserted' is the table that we want it to be the result but what about the 'deleted'. What does it do to my problem.
I should learn more about this, perhaps.Once again, thanks to X002548!

Tuan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources