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.
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 : CustomerSecond table is : CustomerLog> There is a CustomerId on Customer table that is OK and identity (integer)> CustomerLog has two columns: CustomerId and LogIdI 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.CustomerFOR INSERT AS INSERT INTO CustomerLog ( CustomerID) SELECT INSERTED.IDGO You may want to store additional information into the log table such as a timestamp, which customer rep inserted it etc. |
|
|
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 :) |
|
|
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.CustomerFOR INSERT AS INSERT INTO CustomerLog ( LogID, CustomerID) SELECT INSERTED.LogID INSERTED.IDGO 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(); |
|
|
|
|
|
|
|