Click here to Skip to main content
15,891,184 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
CREATE TRIGGER [dbo].[INSERT_IM_ServiceRequest_Hx] ON [dbo].[IM_ServiceRequest] AFTER INSERT
AS

BEGIN

INSERT INTO  IM_ServiceRequest_Hx
SELECT * FROM IM_ServiceRequest
PRINT 'AFTER INSERT Trigger fired.'
END
Posted

Change the definition to be
CREATE TRIGGER [dbo].[INSERT_IM_ServiceRequest_Hx] ON [dbo].[IM_ServiceRequest] AFTER INSERT, UPDATE


Here are some articles you may find useful (on CodeProject)
Overview of SQL Server database Triggers[^]

Triggers -- SQL Server[^]
 
Share this answer
 
v2
Hi,

Here's how to do it (changes in bold):
SQL
CREATE TRIGGER [dbo].[INSERT_IM_ServiceRequest_Hx] ON [dbo].[IM_ServiceRequest] AFTER INSERT, UPDATE
AS

BEGIN

DECLARE @Action AS CHAR(1) = 'I'; -- after insert (by default)
IF EXISTS (SELECT 1 FROM INSERTED) AND EXISTS (SELECT 1 from DELETED)
    SET @Action = 'U'; -- after update
IF (@Action = 'I')
    BEGIN
        INSERT INTO  IM_ServiceRequest_Hx
        SELECT * FROM IM_ServiceRequest
        PRINT 'AFTER INSERT Trigger fired.'
    END
IF (@Action = 'U')
    BEGIN
    -- your code here
    END
END


-------------------------------------------------------------------------------------
Added information:
1. Simplified trigger syntax:
XML
CREATE TRIGGER trigger_name
ON { table | view }
{ FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }

2. There are 2 special tables in trigger statements: inserted and deleted. Inserted table stores copies of the affected rows after INSERT and UPDATE. Deleted table stores copies of the affected rows after DELETE and UPDATE.

AFTER INSERT: 1 or more records in INSERT table, 0 records in DELETE table.
AFTER UPDATE: 1 or more records in INSERT table, 1 or more records in DELETE table.
AFTER DELETE: 0 records in INSERT table, 1 or more records in DELETE table.

Knowing this, you can determine certain event: AFTER INSERT, UPDATE or DELETE.
 
Share this answer
 
v2
Comments
Member 10501509 18-Mar-14 9:41am    
after this if(@action='u') then what should i write can you give sample code.
Andrius Leonavicius 18-Mar-14 9:48am    
You should write your code, which would be executed only AFTER UPDATE.

IF (@Action = 'I') - your code will be executed only AFTER INSERT
IF (@Action = 'U') - your code will be executed only AFTER UPDATE

If you want to execute exactly the same code after insert and update, then use only this:
CREATE TRIGGER [dbo].[INSERT_IM_ServiceRequest_Hx] ON [dbo].[IM_ServiceRequest] AFTER INSERT, UPDATE
AS

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900