这个。。。
手上没有中文版的联机丛书,你可以查一下Transaction的工作原理以及作用,当对某Transaction进行Rollback操作的时候,会将相关数值返回到变更前。
触发器里可以按照你需要的条件写判断语句,不符合条件则返回原始状态,并返回错误信息。
这个。。。
手上没有中文版的联机丛书,你可以查一下Transaction的工作原理以及作用,当对某Transaction进行Rollback操作的时候,会将相关数值返回到变更前。
触发器里可以按照你需要的条件写判断语句,不符合条件则返回原始状态,并返回错误信息。
Trigger语法
Create Trigger [TriggerName] on [TableName]
For Insert|Update|Delete
As
....
If语法
If 判断条件 为真执行语句
else 为假执行语句
end
Because CHECK constraints can reference only the columns on which the column- or table-level constraint is defined, any cross-table constraints (in this case, business rules) must be defined as triggers.
This example creates a trigger that, when an employee job level is inserted or updated, checks that the specified employee job level (job_lvls), on which salaries are based, is within the range defined for the job. To get the appropriate range, the jobs table must be referenced.
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'employee_insupd' AND type = 'TR')
DROP TRIGGER employee_insupd
GO
CREATE TRIGGER employee_insupd
ON employee
FOR INSERT, UPDATE
AS
/* Get the range of level for this job type from the jobs table. */
DECLARE @min_lvl tinyint,
@max_lvl tinyint,
@emp_lvl tinyint,
@job_id smallint
SELECT @min_lvl = min_lvl,
@max_lvl = max_lvl,
@emp_lvl = i.job_lvl,
@job_id = i.job_id
FROM employee e INNER JOIN inserted i ON e.emp_id = i.emp_id
JOIN jobs j ON j.job_id = i.job_id
IF (@job_id = 1) and (@emp_lvl <> 10)
BEGIN
RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)
ROLLBACK TRANSACTION
END
ELSE
IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)
BEGIN
RAISERROR ('The level for job_id:%d should be between %d and %d.',
16, 1, @job_id, @min_lvl, @max_lvl)
ROLLBACK TRANSACTION
END