更新或删除SQL触发器

Question

我必须在UPDATE或DELETE操作中触发一个触发器。当某个列更新时，我的触发器工作正常。但是，我需要不同的逻辑来处理DELETE操作。我如何在一个触发器中同时具有逻辑？这是我到目前为止有：通过观察其magic- /伪表的

ALTER TRIGGER [dbo].[Audit_Emp_Trigger] 
    ON [dbo].[EMPLOYEE] 
    AFTER UPDATE, DELETE 
AS 

BEGIN 
    --Only execute the trigger if the Dno field was updated or deleted 
    IF UPDATE(Dno) 
    BEGIN 

    --If the Audit_Emp_Record table does not exist already, we need to create it 
    IF OBJECT_ID('dbo.Audit_Emp_Record') IS NULL 
    BEGIN 
    --Table does not exist in database, so create table 
    CREATE TABLE Audit_Emp_Record 
    (
    date_of_change smalldatetime, 
    old_Lname varchar (50), 
    new_Lname varchar (50), 
    old_ssn int, 
    new_ssn int, 
    old_dno int, 
    new_dno int 
    ); 

    --Once table is created, insert the values of the update operation into the table 
    INSERT INTO Audit_Emp_Record(date_of_change, old_Lname, new_Lname, old_ssn, new_ssn, old_dno, new_dno) SELECT GETDATE(), D.Lname, I.Lname, D.Ssn, I.Ssn, D.Dno, I.Dno FROM inserted I JOIN deleted D ON I.Ssn = D.Ssn 

    END 

    ELSE 
    BEGIN 
    --The table already exists, so simply insert the new values of the update operation into the table 
    INSERT INTO Audit_Emp_Record(date_of_change, old_Lname, new_Lname, old_ssn, new_ssn, old_dno, new_dno) SELECT GETDATE(), D.Lname, I.Lname, D.Ssn, I.Ssn, D.Dno, I.Dno FROM inserted I JOIN deleted D ON I.Ssn = D.Ssn 
    END 

    END 

END 

Answer 1

可以测试操作的类型 - INSERTED和DELETED在他们的数据。我更喜欢使用类似以下内容：

DECLARE @Operation CHAR(1); 

IF (EXISTS(SELECT * FROM inserted)) 
BEGIN 

    IF (EXISTS(SELECT * FROM deleted)) 
    BEGIN 
     -- rows in both has to be an UPDATE 
     SET @Operation = 'U'; 
    END; 
    ELSE 
    BEGIN 
     -- no rows in "deleted" has to be an INSERT 
     SET @Operation = 'I'; 
    END; 

END; 
ELSE 
BEGIN 
    -- no rows in "inserted" has to be a DELETE 
    SET @Operation = 'D'; 
END; 

然后，您可以使用@Operation变量IF语句做一个或其他的操作。

喜欢的东西：

IF (@Operation = 'U') 
BEGIN 

    --Only execute the trigger if the Dno field was updated or deleted 
    IF UPDATE(Dno) 
    BEGIN 
     {your current code here} 
    END; 
END; 
ELSE 
BEGIN 
    {what to do if the operation is a DELETE goes here} 
END; 

从技术上讲，你不需要ELSE条件，设置@Operation = 'I';，但如果你要复制/将此代码粘贴到各个触发器或保持周围作为模板的话没有恶意在它处理所有三个条件。

而且，就像一个侧面说明，你不需要ELSE条件IF OBJECT_ID('dbo.Audit_Emp_Record') IS NULL声明，也不INSERT INTO Audit_Emp_Record刚好在CREATE TABLE之后，但在END之前。只要执行CREATE TABLE（如果不存在），然后在该测试之外执行INSERT即可。含义：

IF UPDATE(Dno) 
BEGIN 
    --If the Audit_Emp_Record table does not exist already, we need to create it 
    IF OBJECT_ID('dbo.Audit_Emp_Record') IS NULL 
    BEGIN 
     --Table does not exist in database, so create table 
     CREATE TABLE Audit_Emp_Record 
     ... 
    END 

    INSERT INTO Audit_Emp_Record(...) 
END