CREATE TRIGGER mytrigger ON T1 FOR INSERT, UPDATE , DELETE AS DELETE FROM T2 INSERT INTO T2 SELECT * FROM T1 GO
下例创建一个触发器,当插入或更新雇员工作级别 (job_lvls) 时,该触发器检查指定雇员的工作级别(由此决定薪水)是否处于为该工作定义的范围内。若要获得适当的范围,必须引用 jobs 表。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
create trigger triTest on T1 for insert,update as if exists(select 1 from inserted a Inner join T2 b on a.id=b.id) update T2 set Field=b.Field... from inserted b where T2.id=b.id else insert T2 select * from inserted
AS
DELETE FROM T2
INSERT INTO T2 SELECT * FROM T1
GO
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
for insert,update
as
if exists(select 1 from inserted a Inner join T2 b on a.id=b.id)
update T2 set Field=b.Field... from inserted b where T2.id=b.id
else
insert T2 select * from inserted