sql server TRIGGER与逻辑表INSTERED 和 DELETED

      一下内容收集于internet

      触发器语句中使用了两种特殊的表：deleted 表和 inserted 表。Microsoft SQL Server 自动创建和管理这些表。可以使用这两个临时的驻留内存的表测试某些数据修改的效果及设置触发器操作的条件；然而，不能直接对表中的数据进行更改。

    inserted 和 deleted 表主要用于触发器中：  
   
  扩展表间引用完整性。  
   
  在以视图为基础的基表中插入或更新数据。  
   
  检查错误并基于错误采取行动。  
   
  找到数据修改前后表状态的差异，并基于此差异采取行动。  
  Deleted 表用于存储 DELETE 和 UPDATE 语句所影响的行的复本。在执行 DELETE 或 UPDATE 语句时，行从触发器表中删除，并传输到 deleted 表中。Deleted 表和触发器表通常没有相同的行。  
   
  Inserted 表用于存储 INSERT 和 UPDATE 语句所影响的行的副本。在一个插入或更新事务处理中，新建行被同时添加到 inserted 表和触发器表中。Inserted 表中的行是触发器表中新行的副本。  
   
  更新事务类似于在删除之后执行插入；首先旧行被复制到 deleted 表中，然后新行被复制到触发器表和 inserted 表中。  
   
  在设置触发器条件时，应当为引发触发器的操作恰当使用 inserted 和 deleted 表。虽然在测试 INSERT 时引用 deleted 表或在测试 DELETE 时引用 inserted 表不会引起任何错误，但是在这种情形下这些触发器测试表中不会包含任何行。  
   
  说明 如果触发器操作取决于一个数据修改所影响的行数，应该为多行数据修改（基于 SELECT 语句的 INSERT、DELETE 或 UPDATE）使用测试（如检查 @@ROWCOUNT），然后采取相应的对策。  
   
   
  SQL Server™ 2000 不允许 AFTER 触发器引用 inserted 和 deleted 表中的 text、ntext 或 image 列；然而，允许 INSTEAD OF 触发器引用这些列。有关更多信息，请参见 CREATE TRIGGER。  
   
  在 INSTEAD OF 触发器中使用 inserted 和 deleted 表  
  传递到在表上定义的 INSTEAD OF 触发器的 inserted 和 deleted 表遵从与传递到 AFTER 触发器的 inserted 和 deleted 表相同的规则。inserted 和 deleted 表的格式与在其上定义 INSTEAD OF 触发器的表的格式相同。inserted 和 deleted 表中的每一列都直接映射到基表中的列。  
   
  有关引用带 INSTEAD OF 触发器的表的 INSERT 或 UPDATE 语句何时必须提供列值的规则与表没有 INSTEAD OF 触发器时相同：  
  不能为计算列或具有 timestamp 数据类型的列指定值。  
  不能为具有 IDENTITY 属性的列指定值，除非该列的 IDENTITY_INSERT 为 ON。当 IDENTITY_INSERT 为 ON 时，INSERT 语句必须提供一个值。  
  INSERT 语句必须为所有无 DEFAULT 约束的 NOT NULL 列提供值。  
   
  对于除计算列、标识列或 timestamp 列以外的任何列，任何允许空值的列或具有 DEFAULT 定义的 NOT NULL 列的值都是可选的。  
  当 INSERT、UPDATE 或 DELETE 语句引用具有 INSTEAD OF 触发器的视图时，数据库引擎将调用该触发器，而不是对任何表采取任何直接操作。即使为视图生成的 inserted 和 deleted 表中的信息格式与基表中的数据格式不同，该触发器在生成执行基表中的请求操作所需的任何语句时，仍必须使用 inserted 和 deleted 表中的信息。  
   
  传递到在视图上定义的 INSTEAD OF 触发器的 inserted 和 deleted 表格式与为该视图定义的 SELECT 语句的选择列表相匹配。例如：  
   
  CREATE VIEW EmployeeNames (EmployeeID, LName, FName)  
  AS  
  SELECT EmployeeID, LastName, FirstName  
  FROM Northwind.dbo.Employees  
   
  视图的结果集有三列：一个 int 列和两个 nvarchar 列。传递到在视图上定义的 INSTEAD OF 触发器的 inserted 和 deleted 表也具有名为 EmployeeID 的 int 列、名为 LName 的 nvarchar 列和名为 FName 的 nvarchar 列。  
   
  视图的选择列表还包含不直接映射到单个基表列的表达式。一些视图表达式（如常量调用或函数调用）可能不引用任何列，这类表达式会被忽略。复杂的表达式会引用多列，但在 inserted 和 deleted 表中，每个插入的行仅有一个值。如果视图中的简单表达式引用具有复杂表达式的计算列，则这些简单表达式也有同样的问题。视图上的 INSTEAD OF 触发器必须处理这些类型的表达式。有关更多信息，请参见视图上 INSTEAD OF 触发器中的表达式和计算列。 
顺便说一下，当对某张表建立触发器后，分3种情况讨论 
1.插入操作（Insert） 
Inserted表有数据，Deleted表无数据 
2.删除操作（Delete） 
Inserted表无数据，Deleted表有数据 
3.更新操作（Update） 
Inserted表有数据（新数据），Deleted表有数据（旧数据）

 

   利用触发器的INSERTED 可以定义一个无论何时用INSERT语句向表中插入数据时都会执行的触发器。

　　 当触发INSERT触发器时，新的数据行就会被插入到触发器表和inserted表中。inserted表是一个逻辑表，它包含了已经插入的数据行的一个副本。inserted表包含了INSERT语句中已记录的插入动作。inserted表还允许引用由初始化INSERT语句而产生的日志数据。触发器通过检查inserted表来确定是否执行触发器动作或如何执行它。inserted表中的行总是触发器表中一行或多行的副本。

　　 日志记录了所有修改数据的动作(INSERT、UPDATE和DELETE语句)，但在事务日志中的信息是不可读的。然而，inserted表允许你引用由INSERT语句引起的日志变化，这样就可以将插入数据与发生的变化进行比较，来验证它们或采取进一步的动作。也可以直接引用插入的数据，而不必将它们存储到变量中。

　　 示例

　　 在本例中，将创建一个触发器。无论何时订购产品（无论何时向Order Details表中插入一条记录），这个触发器都将更新Products表中的一列(UnitsInStock)。用原来的值减去订购的数量值即为新值。

USE Northwind
CREATE TRIGGER OrdDet_Insert
ON [Order Details]
FOR INSERT
AS
UPDATE P SET
UnitsInStock = P.UnitsInStock – I.Quantity
FROM Products AS P INNER JOIN Inserted AS I
ON P.ProductID = I.ProductID
　　 DELETE触发器的工作过程

　　 当触发DELETE触发器后，从受影响的表中删除的行将被放置到一个特殊的deleted表中。deleted表是一个逻辑表，它保留已被删除数据行的一个副本。deleted表还允许引用由初始化DELETE语句产生的日志数据。

　　 使用DELETE触发器时，需要考虑以下的事项和原则：

　　 ·当某行被添加到deleted表中时，它就不再存在于数据库表中；因此，deleted表和数据库表没有相同的行。

　　 ·创建deleted表时，空间是从内存中分配的。deleted表总是被存储在高速缓存中。

　　 ·为DELETE动作定义的触发器并不执行TRUNCATE TABLE语句，原因在于日志不记录TRUNCATE TABLE语句。

　　 示例

　　 在本例中，将创建一个触发器，无论何时删除一个产品类别（即从Categories表中删除一条记录），该触发器都会更新Products表中的Discontinued列。所有受影响的产品都标记为1，标示不再使用这些产品了。

USE Northwind
CREATE TRIGGER Category_Delete
ON Categories
FOR DELETE
AS
UPDATE P SET Discontinued = 1
FROM Products AS P INNER JOIN deleted AS d
ON P.CategoryID = d.CategoryID
　　 UPDATE触发器的工作过程

　　 可将UPDATE语句看成两步操作：即捕获数据前像(before image)的DELETE语句，和捕获数据后像(after image)的INSERT语句。当在定义有触发器的表上执行UPDATE语句时，原始行（前像）被移入到deleted表，更新行（后像）被移入到inserted表。

　　 触发器检查deleted表和inserted表以及被更新的表，来确定是否更新了多行以及如何执行触发器动作。

　　 可以使用IF UPDATE语句定义一个监视指定列的数据更新的触发器。这样，就可以让触发器容易的隔离出特定列的活动。当它检测到指定列已经更新时，触发器就会进一步执行适当的动作，例如发出错误信息指出该列不能更新，或者根据新的更新的列值执行一系列的动作语句。

　　 语法

IF UPDATE (<column_name>)
　　 例1

　　 本例阻止用户修改Employees表中的EmployeeID列。

USE Northwind
GO
CREATE TRIGGER Employee_Update
ON Employees
FOR UPDATE
AS
IF UPDATE (EmployeeID)
BEGIN
RAISERROR ('Transaction cannot be processed.\
***** Employee ID number cannot be modified.', 10, 1)
ROLLBACK TRANSACTION
END

　　 INSTEAD OF触发器的工作过程

　　 可以在表或视图上指定INSTEAD OF触发器。执行这种触发器就能够替代原始的触发动作。INSTEAD OF触发器扩展了视图更新的类型。对于每一种触发动作(INSERT、UPDATE或 DELETE)，每一个表或视图只能有一个INSTEAD OF触发器。

　　 INSTEAD OF触发器被用于更新那些没有办法通过正常方式更新的视图。例如，通常不能在一个基于连接的视图上进行DELETE操作。然而，可以编写一个INSTEAD OF DELETE触发器来实现删除。上述触发器可以访问那些如果视图是一个真正的表时已经被删除的数据行。将被删除的行存储在一个名为deleted的工作表中，就像AFTER触发器一样。相似地，在UPDATE INSTEAD OF触发器或者INSERT INSTEAD OF触发器中，你可以访问inserted表中的新行。

　　 不能在带有WITH CHECK OPTION定义的视图中创建INSTEAD OF触发器。

 

    获取SQL Server 触发器中insert、update和delete的行（标识id） 在SQL Server中，触发器执行后会产生逻辑表（INSTERED,deleted），如：

INSTERED: inserted

DELETED : deleted

update: inserted,deleted

INSTERED保存的是 insert 或 update 之后新的数据记录形成的表，DELETED保存的是 delete 或 update 之前旧的数据记录形成的表。逻辑表的结构与你建触发器的表相同。可以通过操作逻辑表来获取触发器中insert、update和delete的行（标识id）。

     

      触发器里有两个临时表inserted ,deleted每次触发的时候，都会复制新的数据到inserted，复制旧的数据deleted
如果说是第一次引发触发器的话，inserted,deleted两表里是空的，但是，如果是第二次引发的话，是不是会清空原来在deleted,inserted表里的数据，然后再复制相关的数据呢？

     在进行insert操作时,会触发产生inserted表,进行update操作时,会触发产生inserted和deleted表,在进行delete操作时会产生deleted表.触发器运行时,该两表自动建立,触发器执行完毕,该两表自动删除.不存在清空复制之类的事情.sql2005提供了 output指令，可以在进行insert,delete或update时访问逻辑表。

      用Trigger规范insert, update的值

       

假设有表 A，

CREATE TABLE A(

ID INT NOT NULL IDENTITY(1,1),

ProductID INT NOT NULL,

RetailerID INT NOT NULL,

Date DATETIME NOT NULL

 

CONSTRAINT A_PK PRIMARY KEY (ProductID, RetailerID, Date)

);

我的意思是想让 ProductID, RetailerID 和 Date（不包括小时，分钟等）成为主键。

即：某一天里， retailerID 和 ProductID 是唯一的。

SQLServer 里，DateTime 是可以存储 小时、分钟等的，如果程序只有我自己写，我会规范输入的日期，让其只包含日期部分。但要是直接改数据库，就只有通过触发器来规范了。

 

Oracle 和 MySQL 的触发器都有两个对象： NEW 和 OLD ，但 SQLServer 没有，SQLServer 有 INSTERED 和 DELETED 这两个逻辑表，具体可参考SQLServer 的帮助。

在Oracle 和 MySQL 里，只要用：

NEW.FieldName = XXX;

就可以更改 INSERT 或UPDATE 的值了，在SQLServer 里，目前，我只知道用 UPDATE, 用着很不方便：

CREATE TRIGGER A_T ON A FOR INSERT, UPDATE AS

BEGIN

UPDATE A

SET Date = SUBSTRING(CONVERT(VARCHAR, I.Date ,20),1,10)

FROM INSERTED I

WHERE I.ID = A.ID

END;

好了，INSERT 一条试试：

INSERT INTO A (ProductID, RetailerID, Date) VALUES (1,2,'2009-11-16 12:57:43');

可以看到：

(1 行受影响)

(1 行受影响)

即做了两次操作。。。

不知道还有没有其它方法没有。