Sql Server触发器的使用
创建表:
CREATE TABLE [dbo].[GeneralRule]( [ID] [int] NOT NULL, [GeneralRuleName] [nvarchar](50) NULL, [DeleteFlag] [int] NOT NULL ) CREATE TABLE [dbo].[DetailRule]( [ID] [int] NOT NULL, [DetailRuleName] [nvarchar](50) NULL, [ParentId] [int] NULL, [DeleteFlag] [int] NOT NULL, ) CREATE TABLE [dbo].[DetailRule_bak]( [ID] [int] NOT NULL, [DetailRuleName] [nvarchar](50) NULL, [ParentId] [int] NULL, [DeleteFlag] [int] NOT NULL, )
创建触发器:
--增加 create trigger triAddGeneralRule on DetailRule for insert --为什么事件触发 as begin insert into [DetailRule_bak](ID,detailRuleName,ParentId,DeleteFlag) select ID,detailRuleName,ParentId,DeleteFlag from INSERTED end --删除 create trigger triDelGeneralRule on GeneralRule for delete --为什么事件触发 as delete DetailRule from DetailRule dr,Deleted d where dr.parentId=d.ID --修改 create trigger triGeneralRule on GeneralRule for update --为什么事件触发 as if update (ID) begin update DetailRule set parentId=i.ID from DetailRule dr,Deleted d,Inserted i --2个临时表Deleted和Inserted,分别表示触发事件的旧与新记录 where dr.parentId=d.ID end
if else 触发器
create table employee(emp_id int,emp_name nvarchar(50),gender int,department int,salary numeric(10,2)) create table updated(emp_id int,salary numeric(10,2)) insert into employee values(1,'tom',10,10,6000.00) insert into updated values(1,6500.00) create trigger up_salary on employee INSTEAD OF update as if update (salary) begin declare @newSalary numeric(10,2) declare @oldSalary numeric(10,2) select @newSalary = salary from updated select @oldSalary = salary from employee where emp_id = (select emp_id from updated) if @newSalary > @oldSalary * 1.1 print '工资变动不能超过原来工资的10%' else update employee set salary = @newSalary where emp_id = (select emp_id from updated) end go