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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 After Insert Trigger - Urgent		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

usenetreader79
Starting Member

2 Posts
Posted - 02/15/2013 :  06:46:46  Show Profile  Reply with Quote
Hello,

I have two tables on sql server 2008.

First table is : Customer
Second table is : CustomerLog

> There is a CustomerId on Customer table that is OK and identity (integer)

> CustomerLog has two columns: CustomerId and LogId

I want this:
When a new record inserted in Customer table, a trigger should run, get last inserted identity number of that new record and insert it into the CustomerLog table...

What kind of trigger can do this job and can you write it please...?
Thanks...

James K
Flowing Fount of Yak Knowledge

1500 Posts
Posted - 02/15/2013 :  06:53:47  Show Profile  Reply with Quote
IF the LogID is also an identity column, the trigger can be like this:
CREATE TRIGGER dbo.CustomerLogTrigger ON dbo.Customer
FOR INSERT 
AS 
	INSERT INTO CustomerLog
		( CustomerID)
	SELECT
		INSERTED.ID
GO
You may want to store additional information into the log table such as a timestamp, which customer rep inserted it etc.
Go to Top of Page

usenetreader79
Starting Member

2 Posts
Posted - 02/15/2013 :  07:17:33  Show Profile  Reply with Quote
Hello,

LogId is not identity. CustomerLog tables have CustomerId and LogId columns and both of them are integer.

Does trigger change?

If I want to insert a timestamp as you said then how does your trigger change? (and, LogId is not identity of course)

Thanks and waiting :)
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

1500 Posts
Posted - 02/15/2013 :  07:58:43  Show Profile  Reply with Quote
If LogID is not identity, is there a rule to generating it? Or is it a column in the CustomerID table? If LogID is simply a surrogate key which should be an ever increasing number, it is probably best to make it an identity column. If it is a columnin the Customer table, you can change the trigger like shown below:
CREATE TRIGGER dbo.CustomerLogTrigger ON dbo.Customer
FOR INSERT 
AS 
	INSERT INTO CustomerLog
		( LogID, CustomerID)
	SELECT
		INSERTED.LogID
		INSERTED.ID
GO

To add timestamp, you will need to alter the table first and add another column like this:
ALTER TABLE dbo.Customer ADD InsertTimestamp DATETIME NOT NULL DEFAULT GETDATE();
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.05 seconds. Powered By: Snitz Forums 2000