在 SQL 中为插入后、更新后和删除后创建触发器

2022-01-01 00:00:00 tsql sql-server triggers

我试图在每次插入、更新或删除数据后创建一个 trigger.插入触发器工作正常，但我在更新和删除时遇到了一些问题.这是插入后触发器:

I am trying to create a trigger each time after data is inserted, updated or deleted. The trigger for inserting is working fine but i am having some issues with the Update and Delete. Here is After Insert trigger:

CREATE TRIGGER trgAfterInsert ON  [DR].[dbo].[Derived_Values]
FOR INSERT
AS  
    insert into [Main].[dbo].[Derived_Values_Test]
           (BusinessUnit,Questions, Answer) 
    SELECT BusinessUnit,Questions, Answer
    FROM inserted;

这是我为更新所做的，但似乎是错误的，

This is what I did for the update but it seems wrong,

CREATE TRIGGER trgAfterUpdate ON  [DR].[dbo].[Derived_Values]
FOR UPDATE
AS  

Update [Main].[dbo].[Derived_Values_Test]
set BusinessUnit = 'Updated Record -- After Update Trigger.'; 
GO

如何创建更新和删除触发器?谢谢.

How do i create the update and delete triggers? Thanks.

推荐答案

(更新:忽略了事情中的一个错误，我已更正)

(Update: overlooked a fault in the matter, I have corrected)

(更新2:我凭记忆写了代码搞砸了，修复了它)

(Update2: I wrote from memory the code screwed up, repaired it)

(Update3: 检查 SQLFiddle)

create table Derived_Values
  (
    BusinessUnit nvarchar(100) not null
    ,Questions nvarchar(100) not null
    ,Answer nvarchar(100)
    )

go

ALTER TABLE Derived_Values ADD CONSTRAINT PK_Derived_Values
PRIMARY KEY CLUSTERED (BusinessUnit, Questions);

create table Derived_Values_Test
  (
    BusinessUnit nvarchar(150)
    ,Questions nvarchar(100)
    ,Answer nvarchar(100)
    )

go

CREATE TRIGGER trgAfterUpdate ON  [Derived_Values]
FOR UPDATE
AS  
begin
    declare @BusinessUnit nvarchar(50)
    set @BusinessUnit = 'Updated Record -- After Update Trigger.'

    insert into 
        [Derived_Values_Test]
        --(BusinessUnit,Questions, Answer) 
    SELECT 
        @BusinessUnit + i.BusinessUnit, i.Questions, i.Answer
    FROM 
        inserted i
        inner join deleted d on i.BusinessUnit = d.BusinessUnit
end

go

CREATE TRIGGER trgAfterDelete ON  [Derived_Values]
FOR UPDATE
AS  
begin
    declare @BusinessUnit nvarchar(50)
    set @BusinessUnit = 'Deleted Record -- After Delete Trigger.'

    insert into 
        [Derived_Values_Test]
        --(BusinessUnit,Questions, Answer) 
    SELECT 
        @BusinessUnit + d.BusinessUnit, d.Questions, d.Answer
    FROM 
        deleted d
end

go

insert Derived_Values (BusinessUnit,Questions, Answer) values ('BU1', 'Q11', 'A11')
insert Derived_Values (BusinessUnit,Questions, Answer) values ('BU1', 'Q12', 'A12')
insert Derived_Values (BusinessUnit,Questions, Answer) values ('BU2', 'Q21', 'A21')
insert Derived_Values (BusinessUnit,Questions, Answer) values ('BU2', 'Q22', 'A22')

UPDATE Derived_Values SET Answer='Updated Answers A11' from Derived_Values WHERE (BusinessUnit = 'BU1') AND (Questions = 'Q11');
UPDATE Derived_Values SET Answer='Updated Answers A12' from Derived_Values WHERE (BusinessUnit = 'BU1') AND (Questions = 'Q12');
UPDATE Derived_Values SET Answer='Updated Answers A21' from Derived_Values WHERE (BusinessUnit = 'BU2') AND (Questions = 'Q21');
UPDATE Derived_Values SET Answer='Updated Answers A22' from Derived_Values WHERE (BusinessUnit = 'BU2') AND (Questions = 'Q22');

delete Derived_Values;

然后:

SELECT * FROM Derived_Values;
go

select * from Derived_Values_Test;


Record Count: 0;

BUSINESSUNIT    QUESTIONS   ANSWER
Updated Record -- After Update Trigger.BU1  Q11 Updated Answers A11
Deleted Record -- After Delete Trigger.BU1  Q11 A11
Updated Record -- After Update Trigger.BU1  Q12 Updated Answers A12
Deleted Record -- After Delete Trigger.BU1  Q12 A12
Updated Record -- After Update Trigger.BU2  Q21 Updated Answers A21
Deleted Record -- After Delete Trigger.BU2  Q21 A21
Updated Record -- After Update Trigger.BU2  Q22 Updated Answers A22
Deleted Record -- After Delete Trigger.BU2  Q22 A22

(Update4:如果要同步:SQLFiddle)

create table Derived_Values
  (
    BusinessUnit nvarchar(100) not null
    ,Questions nvarchar(100) not null
    ,Answer nvarchar(100)
    )

go

ALTER TABLE Derived_Values ADD CONSTRAINT PK_Derived_Values
PRIMARY KEY CLUSTERED (BusinessUnit, Questions);

create table Derived_Values_Test
  (
    BusinessUnit nvarchar(150) not null
    ,Questions nvarchar(100) not null
    ,Answer nvarchar(100)
    )

go

ALTER TABLE Derived_Values_Test ADD CONSTRAINT PK_Derived_Values_Test
PRIMARY KEY CLUSTERED (BusinessUnit, Questions);

CREATE TRIGGER trgAfterInsert ON  [Derived_Values]
FOR INSERT
AS  
begin
    insert
        [Derived_Values_Test]
        (BusinessUnit,Questions,Answer)
    SELECT 
        i.BusinessUnit, i.Questions, i.Answer
    FROM 
        inserted i
end

go


CREATE TRIGGER trgAfterUpdate ON  [Derived_Values]
FOR UPDATE
AS  
begin
    declare @BusinessUnit nvarchar(50)
    set @BusinessUnit = 'Updated Record -- After Update Trigger.'

    update
        [Derived_Values_Test]
    set
        --BusinessUnit = i.BusinessUnit
        --,Questions = i.Questions
        Answer = i.Answer
    from
        [Derived_Values]
        inner join inserted i 
    on
        [Derived_Values].BusinessUnit = i.BusinessUnit
        and
        [Derived_Values].Questions = i.Questions
end

go

CREATE TRIGGER trgAfterDelete ON  [Derived_Values]
FOR DELETE
AS  
begin
    delete 
        [Derived_Values_Test]
    from
        [Derived_Values_Test]
        inner join deleted d 
    on
        [Derived_Values_Test].BusinessUnit = d.BusinessUnit
        and
        [Derived_Values_Test].Questions = d.Questions
end

go

insert Derived_Values (BusinessUnit,Questions, Answer) values ('BU1', 'Q11', 'A11')
insert Derived_Values (BusinessUnit,Questions, Answer) values ('BU1', 'Q12', 'A12')
insert Derived_Values (BusinessUnit,Questions, Answer) values ('BU2', 'Q21', 'A21')
insert Derived_Values (BusinessUnit,Questions, Answer) values ('BU2', 'Q22', 'A22')

UPDATE Derived_Values SET Answer='Updated Answers A11' from Derived_Values WHERE (BusinessUnit = 'BU1') AND (Questions = 'Q11');
UPDATE Derived_Values SET Answer='Updated Answers A12' from Derived_Values WHERE (BusinessUnit = 'BU1') AND (Questions = 'Q12');
UPDATE Derived_Values SET Answer='Updated Answers A21' from Derived_Values WHERE (BusinessUnit = 'BU2') AND (Questions = 'Q21');
UPDATE Derived_Values SET Answer='Updated Answers A22' from Derived_Values WHERE (BusinessUnit = 'BU2') AND (Questions = 'Q22');

--delete Derived_Values;

然后:

SELECT * FROM Derived_Values;
go

select * from Derived_Values_Test;


BUSINESSUNIT    QUESTIONS   ANSWER
BU1 Q11 Updated Answers A11
BU1 Q12 Updated Answers A12
BU2 Q21 Updated Answers A21
BU2 Q22 Updated Answers A22

BUSINESSUNIT    QUESTIONS   ANSWER
BU1 Q11 Updated Answers A11
BU1 Q12 Updated Answers A12
BU2 Q21 Updated Answers A21
BU2 Q22 Updated Answers A22

相关文章