SQL Server creates INSERT triggers and updates

Question

I created a table with the following columns: ObservationId, FirstCreatedDate, Description, ...... and LastUpdatedDate in SQL Server 2008 R2. ObservationId is an identifier in increments of 1.

I need to create two triggers, one for INSERT and one for UPDATE. When adding a new record, the INSERT trigger will update the FirstCreatedDate column, receiving the current time; when updating an existing record, the UPDATE trigger updates LastUpdatedDate colunm to get the current datetime.

I was not able to do this because I assume that the identification problem may be a problem.

Can anyone give me a hand? Thanks!

Cheers, Alex

ALTER TRIGGER [dbo].[T_InsertNewObservation] ON [dbo].[GCUR_OBSERVATION] AFTER INSERT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for trigger here UPDATE GCUR_OBSERVATION SET GCUR_OBSERVATION.FirstCreatedDate = getdate() FROM GCUR_OBSERVATION a INNER JOIN INSERTED ins ON a.ObservationId = ins.ObservationId END 

Answer 1

I think you are mostly right, but you are not accessing the INSERTED or DELETED tables correctly.

 ALTER TRIGGER [dbo].[T_InsertNewObservation] ON [dbo].[GCUR_OBSERVATION] AFTER INSERT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- get the last id value of the record inserted or updated DECLARE @id INT SELECT @id = [ObservationId] FROM INSERTED -- Insert statements for trigger here UPDATE GCUR_OBSERVATION SET GCUR_OBSERVATION.FirstCreatedDate = GETDATE() WHERE [ObservationId] = @id END 

PS. Hope this works as I wrote it in a notebook and did not test it.

Answer 2

Below is the code below to AFTER UPDATE to change the last modified date column.

Consult if you have any problems. Thanks!

 ALTER TRIGGER [dbo].[T_UpdateObservation] ON [dbo].[GCUR_OBSERVATION] AFTER UPDATE AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- get the last id value of the record inserted or updated DECLARE @id INT SELECT @id = [ObservationId] FROM DELETED -- Insert statements for trigger here UPDATE GCUR_OBSERVATION SET GCUR_OBSERVATION.LastUpdatedDate = getdate() WHERE [ObservationId] = @id END 

Answer 3

 Create TRIGGER [dbo].[TrigerName] ON [dbo].[TableCreateTriger] FOR INSERT AS INSERT INTO TableInsertDate SELECT * FROM TableCreateTriger