SQL Trigger - 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

General SQL Server Forums

New to SQL Server Programming

SQL Trigger

Author

Topic

tim8w
Starting Member

10 Posts

Posted - 2014-02-28 : 11:57:13

Hi,
I need a trigger to set the creation date of a new record in the database... I tried the following, but it changed all records, not just the new one...

CREATE TRIGGER trgCreationDate
ON [dbo].tabCustomerLookup 
FOR INSERT
AS
BEGIN
	UPDATE tabCustomerLookup
	SET CreationDate = getdate()
END

Any ideas?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-02-28 : 12:42:22

You really should be using a default constraint for this, but basically you need to use the INSERTED table. Something like:

CREATE TRIGGER trgCreationDate
ON [dbo].tabCustomerLookup 
FOR INSERT
AS
BEGIN
	UPDATE tabCustomerLookup
	SET CreationDate = getdate()
	FROM Inserted
	WHERE Inserted.<PK_Column_Name> = tabCustomerLookup.<PK_Column_Name>
END

Obviosuly, replace the <PK_Column_Name> with the right column(s).

tim8w
Starting Member

10 Posts

Posted - 2014-02-28 : 12:48:53

Thanks. I actually just went back and used the Constraint instead...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-03-01 : 10:50:46

quote:
Originally posted by tim8w

Thanks. I actually just went back and used the Constraint instead...

If you want this to happen for UPDATES you will need a trigger. But in this case you may not require it as its createddate
For our tables we have two audit trail column createddate nd modifieddate. Both will have DEFAULT CONSTRAINT based on GETDATE(). In addition modifieddate will be updated using GETDATE() inside AFTER UPDATE trigger to capture UPDATE operation date value.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources