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
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With