March 21, 2007 at 8:58 pm
Hi,
I have a table below
Table A
ID ModDate InputDate Type
1 12/1/05 12/3/05 2
2 1/13/06 1/30/06 5
3 2/14/06 2/23/06 4
and I need to create a trigger to capture only if there is an Update on 'ModDate' with the 'Type' = 5 or an Insert row with the Type ='5'. The trigger will trigger to B table. Please advise. Thanks
March 22, 2007 at 7:38 am
here you go. notice that I created a table to capture what happened, to check if the trigger worked. I guess you may have your logic planned, like send an e-mail or register the event in another table. I don't know the why to find out if the record in <inserted> comes from an update or an insert, that's why I created two triggers. You can check the key of Table_1.ID = inserted.id, if they are the same then is an update, else an insert. Then you will have the logic for just ONE TRIGGER.
You may consider and INSTEAD OF trigger depend on the action you want to take.
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
CREATE
TABLE [dbo].[what_happened](
[whathappened] [nchar]
(10) NULL
)
ON [PRIMARY]
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
CREATE
TRIGGER Insert_Of_Type5
ON Table_1
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
if exists(select * from inserted where [type] = 5)
insert into dbo.what_happened
(whathappened)
values('Insert')
END
GO
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
CREATE
TRIGGER Update_Of_Type5
ON Table_1
AFTER UPDATE,INSERT
AS
BEGIN
SET NOCOUNT ON;
if exists(select * from inserted where [type] = 5)
insert into dbo.what_happened
(whathappened)
values('Update')
END
GO
March 22, 2007 at 10:07 am
What are the differences between AFTER INSERT trigger and INSTEAD OF trigger? Thanks.
March 22, 2007 at 1:20 pm
I want to thank you for help. I have created 2 triggers to differenciate the Insert and Update.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply