我已经为插入后创建了触发器，现在如何在同一个触发器中进行更新 [英] I have created trigger for after insert now how to do for update with in same trigger

问题描述

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

推荐答案

将定义更改为

Change the definition to be

CREATE TRIGGER [dbo].[INSERT_IM_ServiceRequest_Hx] ON [dbo].[IM_ServiceRequest] AFTER INSERT, UPDATE

这里有一些ar你可能觉得有用的词（在CodeProject上）

概述SQL Server数据库触发器 [ ^ ]



触发器 - SQL Server [ ^ ]

您好，



以下是如何操作（粗体更改）：

Hi,

Here's how to do it (changes in bold):

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

------------------------- -------------------------------------------------- ----------

添加信息：

1.简化的触发语法：

-------------------------------------------------------------------------------------
Added information:
1. Simplified trigger syntax:

CREATE TRIGGER trigger_name
ON { table | view }
{ FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }

2.触发器语句中有2个特殊表：已插入和已删除。插入的表在INSERT和UPDATE之后存储受影响的行的副本。删除表存储DELETE和UPDATE后受影响行的副本。



AFTER INSERT：INSERT表中有1个或多个记录，DELETE表中有0个记录。

更新后：INSERT表中有1个或多个记录，DELETE表中有1个或多个记录。

AFTER DELETE：INSERT表中有0个记录，DELETE表中有1个或多个记录。 />


了解这一点，您可以确定某个事件：插入，更新或删除后。

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.

这篇关于我已经为插入后创建了触发器，现在如何在同一个触发器中进行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！