SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Trigger
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tim8w
Starting Member

USA
10 Posts

Posted - 02/28/2014 :  11:57:13  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 02/28/2014 :  12:42:22  Show Profile  Reply with Quote
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).
Go to Top of Page

tim8w
Starting Member

USA
10 Posts

Posted - 02/28/2014 :  12:48:53  Show Profile  Reply with Quote
Thanks. I actually just went back and used the Constraint instead...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/01/2014 :  10:50:46  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000