CODEWITHSUNDEEP
sql-serversql-server-2008-r2
alextc
alextc

Reputation: 3515

SQL Server create Triggers on INSERT and Update

I have created a table with the following columns: ObservationId, FirstCreatedDate, description, ... ... and LastUpdatedDate in SQL Server 2008 R2. The ObservationId is an identity with increment by 1.

I need to create two triggers, one for INSERT and the other for UPDATE. On new record inserted, the INSERT trigger will update FirstCreatedDate column by getting the current datetime; while on existing record updated, the UPDATE trigger will update LastUpdatedDate colunm by getting the current datetime.

I have failed in doing this as I am guessing the identify might be the problem.

Could 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

Upvotes: 8

Views: 92078

Answers (3)

IT Solution
IT Solution

Reputation: 1

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

Upvotes: -2

alextc
alextc

Reputation: 3515

The code below is for AFTER UPDATE to modify last changed date column.

Please advice if you see any issue with it. 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

Upvotes: 1

Kane
Kane

Reputation: 16812

I think you are mostly correct but 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. Hopefully this works as I wrote it in notepad and haven't tested it.

Upvotes: 14

Related Questions