Solved

Update timestamp with Trigger.

Posted on 2006-07-20
1
823 Views
Last Modified: 2008-01-09
I'm a MSSQL novice and I am trying to create a trigger that will copy data as it is inserted into the results table of one DB into a tblBReagleresult table in another DB. I have succeeded in copying the data, but I also want to insert the date and time that the record was updated also. Now I have the syntax listed below but it will modify the time on all records in that field. We are currently using MSSQL 7.0, here is the trigger I have.

CREATE TRIGGER [CopyResults] ON [dbo].[result]
FOR INSERT
AS
INSERT INTO [STOCKYARDS].[dbo].[tblBReagleresult]([ProdID], [Throughput], [OpNr], [ResultNr], [Val], [ValueID])
SELECT [ProdID], [Throughput], [OpNr], [ResultNr], [Val], [ValueID] FROM INSERTED
update [STOCKYARDS].[dbo].[tblBReagleresult]
set [transtime]=getdate()
0
Comment
Question by:power4350
1 Comment
 
LVL 25

Accepted Solution

by:
Mr_Peerapol earned 50 total points
ID: 17151443
CREATE TRIGGER [CopyResults] ON [dbo].[result]
FOR INSERT
AS
INSERT INTO [STOCKYARDS].[dbo].[tblBReagleresult]([ProdID], [Throughput], [OpNr], [ResultNr], [Val], [ValueID], [transtime])
SELECT [ProdID], [Throughput], [OpNr], [ResultNr], [Val], [ValueID], GETDATE() FROM INSERTED
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now