SqlServer触发器的基本使用

本文通过实例展示了SQL触发器的应用,包括DML和DDL触发器的创建及使用,涉及insert、update、delete操作对数据库表的影响和相应触发器的实现。
--触发器分类:
--1,DML触发器
	--1.1 insert         新增的记录保存在inserted表中
	--1.2 update		 旧的记录在deleted中,新的在inserted中
	--1.3 delete         刚刚删除的记录保存在 deleted表中
	--1.4 instead of     用来指定用DML触发器中的操作代替触发语句的操作
--2,DDL触发器   
--练习常用的  insert update delete 触发器
go 
use db_Test;
if exists (select * from sysobjects where name='book')
drop table book;
create table book(
	bookId int primary key,
	bookName char(20),
	bookPrice float,
	publishTime date,
	bookNum int         --书本的剩余数量
);
--借书记录
if exists (select * from sysobjects where name='borrowedBook')
drop table borrowedBook;
create table borrowedBook(
	bookId int,
	borrwDate Date,
	bookName char(20),
	borrowNum int --借书数量
)
go
--创建存储过程,向book表中插入10条数据
if exists (select * from sysobjects where name='addBook')
drop procedure addBook;
go
create procedure addBook as
begin
	declare @i int;
	set @i=1;
	while(@i<10)
	begin
		--cast 将一个表达式转化成另外一个类型
		insert into book select @i,'book '+cast(@i as CHAR(1)),(10+@i)/10.0,GETDATE(),@i;
		set @i=@i+1;
	end
end

go
--创建触发器,当向借书记录中添加一条图书记录时,修改原来书本的剩余数量
if exists (select * from sysobjects where name='borrowBook_trigger')
drop trigger borrowBook_trigger;
go
create trigger borrowBook_trigger on borrowedBook after insert as
begin
	declare @bookId int;
	declare @borrowNum int;
	--从inserted表中得到插入的数据的信息
	select @bookId=bookId,@borrowNum=borrowNum from inserted;
	--修改book表中的信息
	update book set bookNum=bookNum-@borrowNum where bookId=@bookId;
end

--创建触发器,当向借书记录中删除一条图书记录时,修改原来书本的剩余数量
if exists (select * from sysobjects where name='returnBook_trigger')
drop trigger returnBook_trigger;
go
create trigger returnBook_trigger on borrowedBook after delete as
begin
	declare @bookId int;
	declare @borrowNum int;
	--从deleted表中得到插入的数据的信息
	select @bookId=bookId,@borrowNum=borrowNum from deleted;
	--修改book表中的信息
	update book set bookNum=bookNum+@borrowNum where bookId=@bookId;
end

--创建触发器,当修改图书的信息(书名)时候,对应修改借书表中的信息(书名)
if exists (select * from sysobjects where name='updateBook_trigger')
drop trigger updateBook_trigger;
go
create trigger updateBook_trigger on book after update as
begin
	declare @bookId int;
	declare @newName char(20);
	--从inserted表中得到修改的数据的信息
	select @bookId=bookId,@newName=bookName from inserted;
	--修改borrowedBook表中的信息
	update borrowedBook set bookName=@newName where bookId=@bookId;
end
go
-- 测试数据:


--执行存储过程,向book表中插入数据
exec addBook;
--查看图书记录
select * from book;
--查看借书记录
select * from borrowedBook;

--向借书表中插入一条记录 借走id为2的书一本
insert into borrowedBook values (2,GETDATE(),'book2',1);
select * from book;
select * from borrowedBook;

--修改书本id为2的书名
update book set bookName='newBook' where bookId=2;
select * from book;
select * from borrowedBook;

--从借书表中删除记录
delete from borrowedBook where bookId=2;
select * from book;
select * from borrowedBook;

<think>好的,用户想了解如何使用SQL Server触发器,特别是需要教程和示例。首先,我需要明确触发器基本概念和类型。根据引用内容,触发器分为DML触发器和DDL触发器,而DML触发器又包括After触发器和Instead of触发器。用户可能更需要DML触发器的示例,因为这些更常用。 接下来,用户需要具体的创建步骤和示例。根据引用中的例子,比如引用[2]中的创建触发器语句,我可以展示如何创建一个简单的触发器来响应INSERT和DELETE操作。另外,引用[5]提到使用inserted和deleted表,这部分需要详细解释,因为这是触发器的关键机制。 然后,用户可能需要了解触发器的应用场景,比如审计日志、数据校验等。引用[4]提到触发器检查deleted和inserted表,这可能用于数据变化的追踪。同时,引用[3]提到Instead of触发器用于替代原操作,特别是用在视图上,这部分可以作为高级示例。 需要注意语法正确性,比如使用CREATE TRIGGER语句,指定ON子句和FOR事件。还要强调WITH ENCRYPTION和EXECUTE AS等可选参数,如引用[1]中的加密触发器。另外,示例代码的结构和注释需要清晰,确保用户能够理解每一步的作用。 用户可能遇到的问题包括触发器的执行顺序、多行操作的处理,以及如何避免递归触发。引用[4]提到检查deleted和inserted表来确定如何执行动作,这可能需要解释如何处理多行更新。 最后,生成的相关问题应该覆盖触发器的类型、应用场景、管理方法以及常见问题,帮助用户进一步学习。例如,如何选择After和Instead of触发器触发器的性能影响等。</think>### SQL Server触发器使用教程及示例 #### 一、触发器基础概念 触发器是数据库中的特殊存储过程,当特定事件(如INSERT/UPDATE/DELETE)发生时自动执行。其核心作用包括数据完整性校验、审计跟踪、级联操作等[^1]。 #### 二、触发器创建语法 ```sql CREATE TRIGGER [trigger_name] ON [table_name] [FOR/AFTER/INSTEAD OF] [INSERT/UPDATE/DELETE] AS BEGIN -- 逻辑代码 -- 可访问inserted/deleted临时表 END ``` * **FOR/AFTER**:在操作完成后触发(默认类型) * **INSTEAD OF**:替代原操作执行(常用于视图更新)[^3] * **WITH ENCRYPTION**:可选参数,加密触发器定义[^1] #### 三、基础示例 ##### 示例1:审计日志触发器 ```sql CREATE TRIGGER trg_AuditEmployeeChanges ON Employees AFTER INSERT, UPDATE, DELETE AS BEGIN INSERT INTO AuditLog (ChangeType, ChangeTime, UserName) SELECT CASE WHEN inserted.EmployeeID IS NOT NULL AND deleted.EmployeeID IS NULL THEN 'INSERT' WHEN deleted.EmployeeID IS NOT NULL AND inserted.EmployeeID IS NULL THEN 'DELETE' ELSE 'UPDATE' END, GETDATE(), SYSTEM_USER FROM inserted FULL OUTER JOIN deleted ON inserted.EmployeeID = deleted.EmployeeID END ``` * 通过`inserted`和`deleted`表追踪数据变化[^5] * `FULL OUTER JOIN`处理所有操作类型 ##### 示例2:数据校验触发器 ```sql CREATE TRIGGER trg_PreventSalaryDecrease ON Salaries INSTEAD OF UPDATE AS BEGIN IF UPDATE(Salary) BEGIN IF EXISTS ( SELECT * FROM inserted i JOIN deleted d ON i.EmployeeID = d.EmployeeID WHERE i.Salary < d.Salary ) BEGIN RAISERROR('禁止降低员工薪资', 16, 1) ROLLBACK TRANSACTION END ELSE UPDATE s SET s.Salary = i.Salary FROM Salaries s JOIN inserted i ON s.EmployeeID = i.EmployeeID END END ``` * 使用`INSTEAD OF`触发器替代更新操作[^3] * `UPDATE()`函数检测特定列是否被修改 #### 四、高级应用场景 ##### 视图更新触发器 ```sql CREATE TRIGGER trg_UpdateView ON SalesSummaryView INSTEAD OF INSERT AS BEGIN INSERT INTO Orders (ProductID, Quantity) SELECT ProductID, Quantity FROM inserted END ``` * 通过触发器实现视图的写入操作[^3] ##### 级联删除触发器 ```sql CREATE TRIGGER trg_CascadeDelete ON Departments AFTER DELETE AS BEGIN DELETE FROM Employees WHERE DepartmentID IN (SELECT deleted.DepartmentID FROM deleted) END ``` #### 五、触发器管理 ```sql -- 禁用触发器 DISABLE TRIGGER trg_AuditEmployeeChanges ON Employees -- 启用触发器 ENABLE TRIGGER trg_AuditEmployeeChanges ON Employees -- 查看触发器定义 SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID('trg_AuditEmployeeChanges') -- 删除触发器 DROP TRIGGER trg_AuditEmployeeChanges ```
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值