sql服务器触发器 [英] 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:

• 后触发器在插入，更新，删除后触发


• 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拥有的INSERT / UPDATE / DELETE触发器。

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

您可以可以使用任意数量的AFTER触发器，但是每个操作只能使用一个INSTEAD OF触发器（INSERT，UPDATE，DELETE）。

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

在这里，我正在检查插入伪表，其中包含插入到您的所有行中表，对于包含 status = FAIL的每一行，您将在 temp表中插入一些字段。

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服务器触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！