sql server 触发器 [英] sql server trigger

问题描述

我有一个这样的表结构:

I have a table structure like this:

create table status_master
(
  Name varchar(40)
  status varchar(10)
)

如果状态列值更新了值，我需要为状态列创建触发器FAIL 然后触发器调用一个插入命令，如:

I need to create trigger for status column if the status column value updated value FAIL then the trigger invoke one insert commant like:

insert into temp value('s',s's')

你能请任何人给我解决这个问题的想法吗?

Could you please any one give me tha idea to solve this?

推荐答案

不确定您真正想要实现的目标 - 但在 SQL Server 中，您有两种类型的触发器:

Not sure what you really want to achieve - but in SQL Server, you have two types of triggers:

• AFTER 在 INSERT、UPDATE、DELETE 之后触发的触发器
• INSTEAD OF 可以捕获操作(INSERT、UPDATE、DELETE)并执行某些操作的触发器

• AFTER triggers that fire after INSERT, UPDATE, DELETE
• INSTEAD OF triggers which can catch the operation (INSERT, UPDATE, DELETE) and do something instead

SQL Server 没有其他 RDBMS 具有的 BEFORE INSERT/UPDATE/DELETE 触发器.

SQL Server does not have the BEFORE INSERT/UPDATE/DELETE triggers that other RDBMS have.

您可以有任意数量的 AFTER 触发器，但每个操作(插入、更新、删除)只能有一个 INSTEAD OF 触发器.

You can have any number of AFTER triggers, but only one INSTEAD OF trigger for each operation (INSERT, UPDATE, DELETE).

更常见的情况是 AFTER 触发器，例如:

The more common case is the AFTER trigger, something like:

CREATE TRIGGER trgCheckInsertedValues
ON status_master
AFTER INSERT
AS
BEGIN
  INSERT INTO dbo.temp(field1, field2, field3)
     SELECT i.Name, i.Status
     FROM inserted i
     WHERE i.Status = 'FAIL'
END

在这里，我正在检查插入的"伪表，其中包含插入表中的所有行，对于包含状态 = 失败"的每一行，您将向临时"表中插入一些字段.

Here, I am inspecting the "inserted" pseudo-table which contains all rows inserted into your table, and for each row that contains "status = FAIL", you'd be inserting some fields into a "temp" table.

再次 - 不确定您真正想要的细节 - 但这将是如何在 SQL Server T-SQL 代码中执行此操作的粗略概述.

Again - not sure what you really want in detail - but this would be the rough outline how to do it in SQL Server T-SQL code.

马克

这篇关于sql server 触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！