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 2008 Forums
 Transact-SQL (2008)
 After Insert Trigger - Urgent

Author  Topic 

usenetreader79
Starting Member

2 Posts

Posted - 2013-02-15 : 06:46:46
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-15 : 06:53:47
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 - 2013-02-15 : 07:17:33
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-15 : 07:58:43
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
   

- Advertisement -