SQL Server 触发器的问题

[飞行兔子]

用 for update 选项创建的触发器中怎样知道将被改变的行的各个字段值？
比如说：有表spzl，字段：spbh(关键字段),spsl
现在要用一个触发器监视被update语句改变的行的原值
即：当语句update spzl set spsl=1,spbh=123456 where spbh = 12345被执行时
触发器应该知道是spbh = 12345这一行被改变了。。。。

请各位指点，先谢了！

[RedFire]

呵呵，灌水了！

简单说来，你可以在存储过程中用select spbh from deleted得到旧的值；用select spbh from inserted 得到新的值。

E. Use COLUMNS_UPDATED
This example creates two tables: an employeeData table and an auditEmployeeData table. The employeeData table, which holds sensitive employee payroll information, can be modified by members of the human resources department. If the employee’s social security number (SSN), yearly salary or bank account number is changed, an audit record is generated and inserted into the auditEmployeeData audit table.

By using the COLUMNS_UPDATED() function, it is possible to test quickly for any changes to these columns that contain sensitive employee information.

USE pubs

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'employeeData')

DROP TABLE employeeData

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'auditEmployeeData')

DROP TABLE auditEmployeeData

GO

CREATE TABLE employeeData (

emp_id int NOT NULL,

emp_bankAccountNumber char (10) NOT NULL,

emp_salary int NOT NULL,

emp_SSN char (11) NOT NULL,

emp_lname nchar (32) NOT NULL,

emp_fname nchar (32) NOT NULL,

emp_manager int NOT NULL

)

GO

CREATE TABLE auditEmployeeData (

audit_log_id uniqueidentifier DEFAULT NEWID(),

audit_log_type char (3) NOT NULL,

audit_emp_id int NOT NULL,

audit_emp_bankAccountNumber char (10) NULL,

audit_emp_salary int NULL,

audit_emp_SSN char (11) NULL,

audit_user sysname DEFAULT SUSER_SNAME(),

audit_changed datetime DEFAULT GETDATE()

)

GO



CREATE TRIGGER updEmployeeData

ON employeeData

FOR update AS



-- Check whether columns 2, 3 or 4 has been updated. If any or all of

-- columns 2, 3 or 4 have been changed, create an audit record.

-- The bitmask is: power(2,(2-1))+power(2,(3-1))+power(2,(4-1)) = 14

-- To check if all columns 2, 3, and 4 are updated, use = 14 in place of

-- >0 (below).



IF (COLUMNS_UPDATED() & 14) > 0

-- Use IF (COLUMNS_UPDATED() & 14) = 14 to see if all of columns 2, 3,

-- and 4 are updated.

BEGIN

-- Audit OLD record.

INSERT INTO auditEmployeeData

(audit_log_type,

audit_emp_id,

audit_emp_bankAccountNumber,

audit_emp_salary,

audit_emp_SSN)

SELECT 'OLD',

del.emp_id,

del.emp_bankAccountNumber,

del.emp_salary,

del.emp_SSN

FROM deleted del



-- Audit NEW record.

INSERT INTO auditEmployeeData

(audit_log_type,

audit_emp_id,

audit_emp_bankAccountNumber,

audit_emp_salary,

audit_emp_SSN)

SELECT 'NEW',

ins.emp_id,

ins.emp_bankAccountNumber,

ins.emp_salary,

ins.emp_SSN

FROM inserted ins

END



GO



--Inserting a new employee does not cause the UPDATE trigger to fire.

INSERT INTO employeeData

VALUES ( 101, 'USA-987-01', 23000, 'R-M53550M', N'Mendel', N'Roland', 32)

GO



-- Updating the employee record for employee number 101 to change the

-- salary to 51000 causes the UPDATE trigger to fire and an audit trail

-- to be produced.



UPDATE employeeData

SET emp_salary = 51000

WHERE emp_id = 101

GO

SELECT * FROM auditEmployeeData

GO



--Updating the employee record for employee number 101 to change both the bank account number and social security number (SSN) causes the UPDATE trigger to fire and an audit trail to be produced.



UPDATE employeeData

SET emp_bankAccountNumber = '133146A0', emp_SSN = 'R-M53550M'

WHERE emp_id = 101

GO

SELECT * FROM auditEmployeeData

GO

[飞行兔子]

感谢各位的鼎力相助！！！！！！！！

[飞行兔子]

敬请关注！

[FamelStudio]

使用临时表：deleted或者inserted。比如：

CREATE TRIGGER truCheck ON spzl FOR UPDATE AS
IF UPATE(spbh)
IF inserted.spbh < 1000
RAISEERROR “Invalid number!”
GO