CREATE TRIGGER <trigger_name, sysname, trig_test> ON <table_name, sysname, pubs.dbo.sales> FOR DELETE, INSERT, UPDATE AS BEGIN RAISERROR (50009, 16, 10) ENDALTER 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, jobs j, inserted i where e.emp_id = i.emp_id AND i.job_id = j.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触发器是在数据库中的数据变动时(insert ,update ,delete)自动运行的一种存储过程.
ON <table_name, sysname, pubs.dbo.sales>
FOR DELETE, INSERT, UPDATE
AS
BEGIN
RAISERROR (50009, 16, 10)
ENDALTER 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, jobs j, inserted i
where e.emp_id = i.emp_id AND i.job_id = j.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触发器是在数据库中的数据变动时(insert ,update ,delete)自动运行的一种存储过程.