SQL查询艺术学习笔记--SQL触发器

--SQL触发器
--说明：
--触发器是数据库独立的对象存储，由一个事件来启动运行。不能接收参数 触发器作用：保证参照的完整性和数据的一致性
--触发器分为：After触发器和Instead of触发器
--instead of触发器只执行触发器本身，定义于表或视图.不作用于insert delete updata语句
--instead of触发器一个表只能存在一个，但可以通过表创建多个视图对应不同的instead of 触发器
--After触发器分为三种类型:Insert  Update   Delete操作触发器
--关于触发器执行环境：
--包括触发器本身的细节和触发器所定义的目标表。同时，触发器还包括任意以下测试表：inserted 表   deleted 表
--测试表为虚表，保存目标表更新、插入或删除的数据信息。测试表用来测试数据修改的结果及设置触发器行动的条件。
--使用者不能直接修改表的数据，可以通过select来检测结果。


--deleted表：存放delete和update语句中相关行的副本，在delete或updata语句执行中
--将这些相关行从trigger表中移到deleted表中,通常情况两表无共同行<行:行数据>


--inserted表：存放了insert和update语句的相关行的副本，在insert和update执行时，这些新行同时被加到insted表和
--update表中。inserted个中的行是trigger表中新行的副本。


--update操作等于一个delete再加insert，在执行UPDATE时，先将需要修改的行得制到delete表中，然后新行被复制到inserted表
--和trigger表中。
--补充一下：
-- ()  小括号表示必须的。
-- < >  尖括号，用于分隔字符串，字符串为语法元素的名称，SQL语言的非终结符。
-- ::=  定义操作符。用在生成规则中，分隔规则定义的元素和规则定义。 被定义的元素位于操作符的左边，规则定义位于操作符的右边。
-- [ ]   方括号表示规则中的可选元素。方括号中的规则部分可以明确指定也可以省略。
-- { }   花括号聚集规则中的元素。在花括号中的规则部分必须明确指定。
-- |     替换操作符。 该竖线表明竖线之后的规则部分对于竖线之前的部分是可替换的。 如果竖线出现的位置不在花括号或方括号内，
--       那么它指定对于该规则定义的元素的一个完整替换项。如果竖线出现的位置在花括号或方括号内，那么它指定花括号对或方括号对最里面内容的替换项。
--  . . . 省略号表明在规则中省略号应用的元素可能被重复多次。如果省略号紧跟在闭花括号"}"之后，那么它应用于闭花括号和开花括号"{"之间的规则部分。
--        如果省略号出现在其他任何元素的后面，那么它只应用于该元素。


--查看当前数据库下的触发器：
SELECT * FROM Sysobjects WHERE xtype = 'TR'
SELECT * into alltrigger FROM Sysobjects WHERE xtype = 'TR'
--查询当前数据库中触发器列表至alltrigger表
select * from alltrigger
--查看当前数据库下的触发器及关联表：
SELECT tb2.name AS '表名',tb1.name AS '表触发器名'
FROM Sysobjects tb1 JOIN Sysobjects tb2 ON tb1.parent_obj=tb2.id WHERE tb1.type='TR'


select tb2.name as '表名',tb1.name as '表触发器名' into triggertable from sysobjects tb1 join sysobjects tb2
on tb1.parent_obj=tb2.id where tb1.type='tr'
--将当前数据库下触发器及关联表写入至另一张表中
select * from triggertable


--查询触发器
sp_helptext 'trigger_name'
--列查询update_icmrpresult触发器
sp_helptext 'routing_del'




--触发器创建语法：
create trigger tigger_name
on [|table|view]
[with encryption]
{
{{for|after|instead of}{[delete][,][insert] [,][update]}
      [not for replication]
--replication:复制，应答.反响
as 
[{if update(column)
     [{and|or} update {column}]
     [...n]
[if(columns_updated() {bitwise_operator} updated_bitmask)
   {comparison_operator} column_bitmarsk[...n]}]
--bitwise:按位 bitmask:位掩码
--comparison:比较 对照 比较关系 operator:操作者
sql_statement[....n]
}
}
--语法说明：
--trigger_name;创建的触发器名字，符合命名规则，在当前数据库中是唯一的
--table view: 与触发器相关联的表或视图的名字，前提是该表和视图已经在数据库中存在。
--with encryption:对含create trigger文本的syscomments表进行加密，阻止用户查询该表得到触发器的代码。
--after:指在执行了指定的操作（insert delete update）之后，触发器才被激活执行触发器中的SQL语句。
--for：表示为after触发器，并且该触发器仅能在表上创建。
--instead of：指定的触发器为instead of 触发器
--instead:替代 相反
--delete, insert, update:指明执行那种操作将激活触发器，至少要包含3种操作类型中的一种。也可以是3种操作语句中的任意顺序组合，
--各选项以逗号分隔。
--not for replication:通知数据库管理系统（dbms)，复制表时触发器不执行。
--as:在这之后列出触发器要执行的动作。
--if update column:测定对某一确定列是insert操作还是update操作。如果测试的
--insert或者update操作的列多于一列，可用and或者or逻辑连接向if update 子句中添加所希望的附加列名。
--if columns_updated():仅在insert和update类型的触发器中使用，检查列是被更新还是被插入。
--bitwise_operator:代表位逻辑运算符：‘&'符号
--updated_bitmask:表示列的整位掩码，其中最右边的位表示表或视图的第1列，左边第2位表示第2列，依次类推。
--comparison_operator:表示比较操作符’=' 和'>' '='表示栓查晨updated_bitmask中定义的所有列是否都有更新。
--    '>'表示检查是否在update_bitmarsk中定义的某些列被更新。 
--comparison:比较
--column_bitmarsk：该字段是要检列的整型位掩码，用来检查是否已更新或插入了这些列。
--sql_statement:代包表含在触发器中的处理语句。


--insert触发器
 create trigger s_insert
 on student
 --指明操作表
 for insert as
 declare @s_no varchar(4),@s_cno int
 select @s_no=sno,@s_cno=cno
 from inserted
 if(left(@s_no,2)!='97')
 begin
 rollback transaction
 raiserror('输入的学号：%s不是97级的学生，请确认后重新输入!',16,1,@s_no)
 --%s表示的是字符串？16,1错误严重级别？
 end
 if(@S_cno not in(select cno from course))
 begin
 rollback transaction
 raiserror('输入的课程号：%d在course表中不存在，请确认后重新录入!',16,1,@s_cno)
 --%d表示的是整数？
 end
 --补充:SQL raiserror: %d:数值 %s:字符  %i:原样输出 详细看下面：
 
 --补充关于：raiserror
 -- 参考页面：https://docs.microsoft.com/zh-cn/sql/t-sql/language-elements/raiserror-transact-sql
 ---- Syntax for SQL Server and Azure SQL Database  
RAISERROR ( { msg_id | msg_str | @local_variable }  
    { ,severity ,state }  
    [ ,argument [ ,...n ] ] )  
    [ WITH option [ ,...n ] ]
/*参数
msg_id
用户定义的错误消息号存储在使用 sp_addmessage sys.messages 目录视图中。 用户定义的错误消息的错误号应大于 50000。
 当msg_id未指定，则 RAISERROR 引发一条错误消息，50000 错误号。
msg_str
是用户定义的消息格式必须为类似于printf C 标准库中的函数。 该错误消息最长可以有 2,047 个字符。 
如果该消息包含的字符数等于或超过 2,048 个，则只能显示前 2,044 个并添加一个省略号以表示该消息已被截断。
请注意，由于内部存储行为的缘故，代替参数使用的字符数比输出所显示的字符数要多。
例如，替换参数的%d使用 2 赋的值实际生成的消息字符串中的一个字符，但却还内部占用的存储的三个其他字符。
此存储要求减少了可用于消息输出的字符数。
当msg_str指定，则 RAISERROR 引发一条错误消息，50000 错误号。
msg_str是使用可选的嵌入的转换规范的字符的字符串。
每个转换规范定义如何格式化和放入一个字段中的转换规范位置自变量列表中的值是msg_str。 转换规格的格式如下：
%[[标志] [宽度] [。 精度] [{h | l}]]类型
可以在中使用的参数msg_str是：
标志
用于确定被替换值的间距和对齐的代码。
代码 前缀或对齐 Description
-（减） 左对齐 在给定字段宽度内左对齐参数值。
+ （加） 符号前缀 如果参数值为有符号类型，则在参数值的前面加上加号（+）或减号（-）。
0（零） 零填充 在达到最小宽度之前在输出前面加上零。 如果出现 0 和减号 (-)，将忽略 0。
#（数字） 对 x 或 X 的十六进制类型使用 0x 前缀当使用 o、x 或 X 格式时，数字符号 (#) 标志在任何非零值的前面分别加上 0、0x 或 0X。 当 d、i 或 u 的前面有数字符号 (#) 标志时，将忽略该标志。
' '（空白） 空格填充 如果输出值有符号且为正，则在该值前加空格。 如果包含在加号（+）标志中，则忽略该标志。
宽度
定义放置参数值的字段的最小宽度的整数。 如果参数值的长度是等于还是长于宽度，没有空白打印值。 如果值为短于宽度，值填充到中指定的长度宽度。
星号 () 表示宽度由参数列表中的相关参数指定，该宽度必须为整数值。
精度
从字符串值的参数值中得到的最大字符数。 例如，如果一个字符串具有五个字符并且精度为 3，则只使用字符串值的前三个字符。
对于整数值，精度是最小位数打印。
星号 () 表示精度由参数列表中的相关参数指定，该精度必须为整数值。
{h | l}类型
用于字符类型 d i、 o、 s、 x、 X 或 u，并创建shortint (h) 或longint (l) 值。
类型规范 表示
d 或 i 带符号的整数
o 无符号的八进制数
s 字符串
u 无符号的整数
x 或 X 无符号的十六进制数
*/
--测试 因表中数据存在重复值，按以下操作。
select * from teacher2
alter table teacher2  add  xh int default 1
--添加字段（自增字段：indentity
alter table teacher2 add xh int identity(1,1) not null


--添加自增字段：一个表中只能插入一个自增自段，指明开始值，每次增加量
--关于indentity:https://docs.microsoft.com/zh-cn/sql/t-sql/statements/create-table-transact-sql-identity-property
--语法：indentity[(seed,inrement)]
--seed:种子；根据；精液；萌芽；子孙；原由 在这里表示为开始值
--increment:增量；增加；增额；盈余 增加的量
select * from teacher2
alter table teacher2 drop column xh
--删除表的字段
alter table teacher2 alter column xh int  not null
alter table teacher2 add xh int identity(1,1) not null
--修改约束
alter table teacher2 add  constraint pkxh primary key (xh) 
--创建主键约束
drop  table teacher2


select * into teacher2 from teacher
--复制表，原表不存在 用select into
insert into teacher2 select * from teacher
--复制表数据，原表存来用insert into
--再次复习删除表中的重复值：
select * from teacher2 where tno in (select tno from teacher2 group by tno having count(tno)>1)
select  distinct * into #ttable from teacher2
drop table teacher2
select * into teacher2 from #ttable
drop table #ttable


--触发器的删除语法：
drop trigger trigger_name [...n]
drop trigger s_insert
--删除上述表


--delete触发器
--use seldata
create trigger S_delete
on student
for delete as 
declare @rowcount int
select @rowcount=@@rowcount
--@@rowconut:全局函数返回受上一语句影响的行数。 如果多个 20 亿行数，则使用ROWCOUNT_BIG
if @rowcount>1
begin
rollback transaction
raiserror('当前删除的记录数为%d,一次只允许删除一行记录!',16,1,@rowcount)
end
declare @s_dname varchar(16),@s_cno int
select @s_dname=dname,@s_cno=cno
from deleted
if (@s_cno in (select cno from teacher where dname=@s_dname))
begin
rollback transaction
raiserror('删除记录的课程为本系即%s系教师所开设，不允许删除!',16,1,@s_dname)
end


drop trigger s_delete
select * from student
delete from student where sno='9701'
delete from student where sno='9705' and cno=8
select * from teacher
----复习一下连接
--简单双表连接 join
select a.sno, a.sname,  a.dname, a.cno,b.tno, b.dname from
student as a ,teacher as b where a.cno=b.cno order by sno
--内连接（inner join)
--分为等值连接和非等值连接<连接条件不同>
select a.sno,a.sname,a.dname,b.tno,b.dname from 
student as a inner join  teacher as b on a.cno=b.cno order by sno
--外连接（out join）
select a.sno ,a.sname,a.dname,a.cno,b.dname from student as a
left join teacher as b on a.cno=b.cno order by sno
--左连接
select a.sno ,a.sname,a.dname,a.cno,b.dname from student as a
right join teacher as b on a.cno=b.cno order by sno
--右连接
select a.sno ,a.sname,a.dname,a.cno,b.dname from student as a
full join teacher as b on a.cno=b.cno order by sno
--全连接


---update触发器
--update操作分为两部分：需要更新的内容从表中删除，然后再插入新值，因些UPDATE触发器同时涉及到删除表和插入表
--创建跟踪信息表
create table c_upinfo
(
 oper_time smalldatetime,
 date_type char(3),
 cno int,
 cname char(30),
 ctime int,
 scount int,
 ctest smalldatetime,)


select * from c_upinfo
sp_rename 'c_upinfo.c_upinfo.scount', 'count' ,'column'
--修改列名
alter table c_upinfo  drop column [c_upinfo.count]
--删除列
 --注：在删除具有特定属性字段时，可以加上[] 
 alter table c_upinfo add  scount int
 --添加列
 
 --创建update 触发器
 --select * from course
 create trigger c_update
 on course
 for update as
 if(columns_updated()&2)>0 --第一列或第2列更新，掩码 11用十进制表示为3
 begin
 rollback transaction
 raiserror('course表的cno列和cname列中的数据不允许被更新!',16,1)
 end
 if(columns_updated()&30)>0 --第3 4 5 列被更新 掩码 11100用十进制表示为28
 begin
 insert into c_upinfo(oper_time,date_type,cno,cname,ctime,scount,ctest)
 select getdate(),'old',del.cno,del.cname,del.ctime,del.scount,del.ctest from deleted as del
 --将inserted表中存放的记录，即更新后的记录存入upinfo
 insert into c_upinfo(oper_time,date_type,cno,cname,ctime,scount,ctest)
 select getdate(),'new',ins.cno,ins.cname,ins.ctime,ins.scount,ins.ctest from inserted as ins
 end
 
 --测试
 select * from course
 update course set cname='线性代数基础' where cno=4
 --不允许更新1 2列，操作失败
 update course set ctime=32,scount=93,ctest='2017-10-20' where cno=4
 --允许更新 345列，更新成功
 select * from course where cno=4
 select * from c_upinfo
 delete from c_upinfo
 --查询通过触发器写入跟踪表内记录的内容
 drop trigger c_update
 
 --关于位运算
 --参考：https://docs.microsoft.com/zh-cn/sql/t-sql/functions/columns-updated-transact-sql
 /*
 COLUMNS_UPDATED 针对多列执行的 UPDATE 或 INSERT 操作的进行测试。 若要对其进行更新或插入一个列上尝试测试，使用update （)。
COLUMNS_UPDATED 返回一个或多个从左至右排序的字节，每字节中最不重要的位位于最右侧。 最左侧字节的最右侧位表示表中的第一列；向左的下一位表示第二列，依此类推。 如果创建了触发器的表包含八列以上，则 COLUMNS_UPDATED 返回多个字节，最左侧的为最不重要的字节。 在 INSERT 操作中 COLUMNS_UPDATED 将对所有列返回 TRUE，因为这些列插入了显式值或隐式 (NULL) 值。
若要测试针对特定列的更新或插入操作，请遵循使用位运算符和所测试列的整数位掩码的语法。 例如，表t1包含列C1， C2， C3， C4，和C5. 若要验证该列C2， C3，和C4是否所有更新 (与表t1具有 UPDATE 触发器)，按照与语法和 14。 若要测试是否唯一列C2是更新，指定& 2。
可以在 Transact-SQL INSERT 或 UPDATE 触发器内部的任意位置使用 COLUMNS_UPDATED。
INFORMATION_SCHEMA.COLUMNS 视图的 ORDINAL_POSITION 列与 COLUMNS_UPDATED 所返回列的位模式不兼容。 若要获取与 COLUMNS_UPDATED 兼容的位模式，请在查询 ColumnID 视图时引用 COLUMNPROPERTY 系统函数的 INFORMATION_SCHEMA.COLUMNS 属性，如以下示例所示。


复制
SELECT TABLE_NAME, COLUMN_NAME,  
    COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),  
    COLUMN_NAME, 'ColumnID') AS COLUMN_ID  
FROM AdventureWorks2012.INFORMATION_SCHEMA.COLUMNS  
WHERE TABLE_NAME = 'Person';  
列集
对表定义了列集后，COLUMNS_UPDATED 函数的行为如下所述：
当显式更新作为列集成员的列后，该列的对应位将设置为 1，列集的对应位也将设置为 1。
显式更新列集后，列集的对应位将设置为 1，该表中的所有稀疏列的对应位也将设置为 1。
对于插入操作，所有位都将设置为 1。
因为对列集的更改将导致列集中所有列的位设置为 1，所以列集中没有更改的列将显示为已修改。 有关列集的详细信息，请参阅 使用列集。
示例
A. 使用 COLUMNS_UPDATED 测试表的前八列
下面的示例创建两个表：employeeData和auditEmployeeData。 人力资源部的成员可以修改 employeeData 表，该表包含敏感的雇员薪水信息。 如果更改了雇员的社会保险号码 (SSN)、年薪或银行帐户，则生成审核记录并插入到 auditEmployeeData 审核表。
通过使用 COLUMNS_UPDATED()，可以快速测试对包含敏感雇员信息的列所进行的任何更改。 仅当您尝试检测对表的前八列进行的更改时，以这种方式使用 COLUMNS_UPDATED() 才有效。


复制
USE AdventureWorks2012;  
GO  
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  
   WHERE TABLE_NAME = 'employeeData')  
   DROP TABLE employeeData;  
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  
   WHERE TABLE_NAME = 'auditEmployeeData')  
   DROP TABLE auditEmployeeData;  
GO  
CREATE TABLE dbo.employeeData (  
   emp_id int NOT NULL PRIMARY KEY,  
   emp_bankAccountNumber char (10) NOT NULL,  
   emp_salary int NOT NULL,  
   emp_SSN char (11) NOT NULL,  
   emp_lname nchar (32) NOT NULL,  
   emp_fname nchar (32) NOT NULL,  
   emp_manager int NOT NULL  
   );  
GO  
CREATE TABLE dbo.auditEmployeeData (  
   audit_log_id uniqueidentifier DEFAULT NEWID() PRIMARY KEY,  
   audit_log_type char (3) NOT NULL,  
   audit_emp_id int NOT NULL,  
   audit_emp_bankAccountNumber char (10) NULL,  
   audit_emp_salary int NULL,  
   audit_emp_SSN char (11) NULL,  
   audit_user sysname DEFAULT SUSER_SNAME(),  
   audit_changed datetime DEFAULT GETDATE()  
   );  
GO  
CREATE TRIGGER dbo.updEmployeeData   
ON dbo.employeeData   
AFTER UPDATE AS  
/*Check whether columns 2, 3 or 4 have been updated. If any or all  
columns 2, 3 or 4 have been changed, create an audit record. The
bitmask is: power(2,(2-1))+power(2,(3-1))+power(2,(4-1)) = 14. To test   
whether all columns 2, 3, and 4 are updated, use = 14 instead of >0  
(below).*/


   IF (COLUMNS_UPDATED() & 14) > 0  
/*Use IF (COLUMNS_UPDATED() & 14) = 14 to see whether all columns 2, 3,   
and 4 are updated.*/  
      BEGIN  
-- Audit OLD record.  
      INSERT INTO dbo.auditEmployeeData  
         (audit_log_type,  
         audit_emp_id,  
         audit_emp_bankAccountNumber,  
         audit_emp_salary,  
         audit_emp_SSN)  
         SELECT 'OLD',   
            del.emp_id,  
            del.emp_bankAccountNumber,  
            del.emp_salary,  
            del.emp_SSN  
         FROM deleted del;  


-- Audit NEW record.  
      INSERT INTO dbo.auditEmployeeData  
         (audit_log_type,  
         audit_emp_id,  
         audit_emp_bankAccountNumber,  
         audit_emp_salary,  
         audit_emp_SSN)  
         SELECT 'NEW',  
            ins.emp_id,  
            ins.emp_bankAccountNumber,  
            ins.emp_salary,  
            ins.emp_SSN  
         FROM inserted ins;  
   END;  
GO  


/*Inserting a new employee does not cause the UPDATE trigger to fire.*/  
INSERT INTO employeeData  
   VALUES ( 101, 'USA-987-01', 23000, 'R-M53550M', N'Mendel', N'Roland', 32);  
GO  


/*Updating the employee record for employee number 101 to change the   
salary to 51000 causes the UPDATE trigger to fire and an audit trail to   
be produced.*/  


UPDATE dbo.employeeData  
   SET emp_salary = 51000  
   WHERE emp_id = 101;  
GO  
SELECT * FROM auditEmployeeData;  
GO  


/*Updating the employee record for employee number 101 to change both   
the bank account number and social security number (SSN) causes the   
UPDATE trigger to fire and an audit trail to be produced.*/  


UPDATE dbo.employeeData  
   SET emp_bankAccountNumber = '133146A0', emp_SSN = 'R-M53550M'  
   WHERE emp_id = 101;  
GO  
SELECT * FROM dbo.auditEmployeeData;  


GO  
B. 使用 COLUMNS_UPDATED 测试八个以上的列
若要对影响表中前八列以外的列的更新进行测试，请使用 SUBSTRING 函数测试 COLUMNS_UPDATED 返回的更正位。 下面的示例测试影响的字段的更新3， 5，和9中AdventureWorks2012.Person.Person表。


复制
USE AdventureWorks2012;  
GO  
IF OBJECT_ID (N'Person.uContact2', N'TR') IS NOT NULL  
    DROP TRIGGER Person.uContact2;  
GO  
CREATE TRIGGER Person.uContact2 ON Person.Person  
AFTER UPDATE AS  
    IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1) & 20 = 20)   
        AND (SUBSTRING(COLUMNS_UPDATED(),2,1) & 1 = 1) )   
    PRINT 'Columns 3, 5 and 9 updated';  
GO  


UPDATE Person.Person   
   SET NameStyle = NameStyle,  
      FirstName=FirstName,  
      EmailPromotion=EmailPromotion;  
GO  
*/
--instead of 触发器
--insted of 触发器可以让不能更新的视图支持更新操作，由于基于多表的视图不能
--被更新，但我们可以通过insted of 触发器实现此功能
create table stu97
(
sno char(5),
sname char(8),
age int 
)
create table stu98
(
sno char(8),
sname char(8),
age int
)
create table stu99
(
sno char(5),
sname char(8),
age int
)
--创建stu 97 98 99三张表
create view stu_view
as select * from stu97
union all
select * from stu98
union all 
select * from stu99
--创建三张表联合视图 
select * from stu_view


create trigger stu_instead
--创建instead 触发器
on stu_view
instead of insert as
begin
declare @s_no char(2)
--@s_no char(2)用于存放学号sno的前两位,以判断插入记录属于那一张表
select @s_no=substring(sno,1,2) from inserted
--substring函数 语法：substring(expression,strat,length)
--expression 是字符，二进制 文本 ntext 或者映像表达式 strat起始位置 length：自
--起始位置起取长度。
if @s_no='97' 
--由学号判断该学生属于97级学生，记录插入sut97表中
 begin
insert into stu97
select sno,sname,age
from inserted
return
 end
if @s_no='98'
--由学号判断该学生属于98级学生，记录插入stu98表中
 begin
insert into stu98
select sno,sname,age
from inserted
return
  end
if @s_no='99'
--由学号判断该学生属于99级学生，记录插入stu99表中
 begin
insert into stu98
select sno,sname,age
from inserted
  end
else
 begin
rollback transaction
raiserror('插入记录的学号信息不正确，请确认学号为：97 98 99',16,1)
  end
end


--测试
insert into stu_view values('97001','王二狗',22)
--会同时更新视图stu_view和stu97表
select * from stu_view
select * from stu97
insert into stu_view values('11332','汪汪',23)
--会提示写入数据不是按学号规则，不会写入数据视图及表


--嵌套触发器
--Oracle不支持，MSsql支持32层嵌套触发器，如一个表触发器修改某个表，这个表已经存在
--触发器的情况下，会使用该触发器，可用sp_configure来进行设置是启用或关闭？
create table author
(
autor char(8),
hoem char(10),
age int
)
--创建表作者表
create table book
(
isbn int,
name char(20),
author char(8),
price decimal(8,2)
)


--创建书目录表
insert into author values ('王刚','北京',52)
insert into author values ('李华','上海',40)
insert into author values ('任鸣','天津',39)
insert into author values ('李彤','西安',42)
--写入数据
insert into book values('1001','信号处理','李华',32.00)
insert into book values('1002','图像处理','王刚',42.00)
insert into book values('1003','数据库结构','任鸣',18.00)
insert into book values('1004','编译原理','李彤',24.6)
insert into book values('1005','信号与系统','李华',32)
select * from author  as a 
left join book as b on a.author=b.author
sp_rename 'author.autor',   'author',   'column'
--更改了列名
select * from book a left join author b on a.author=b.author


create trigger a_delete
on author
for delete as
declare @rowcount int
select @rowcount=@@rowcount
--记录删除操作所涉及的行数
if @rowcount>1 
--删除记录大于一行
begin
rollback transaction 
--回滚操作
raiserror('当前要删除的记录数为d%,一次只能删除一行记录！',16,1,@rowcount)
end
else
declare @author char(8)
select @author =author
--记录删除记录的author列信息
from deleted
delete book where author=@author
--从book表中删除author对应的记录
--创建author表中的delete触发器a_delete


create trigger B_delete
on book
for delete as
declare @rowcount int
select @rowcount=@@rowcount
if @rowcount>1
begin
rollback transaction
raiserror('当前要删除的记录数为d%,一次只允许删除一条记录！',16,1,@rowcount)
end
else
print 'athor表和book表中的相应数据将会删除'
--创建book表中的触发器b_delete
delete author where author='李华'
--会提示数据不止一条不允许删除
delete author where author='李彤'
--提示所有数据都会删除，2行受影响。
select * from author a left join book b on a.author=b.author


--递归触发器
--递归触发器分为两种：间接递归和直接递归
--间接递归：是对表1操作触发trigger1，trigger1对表2操作引发表2的trigger2，trigger2再对表1操作引发trigger1....
--直接递归：对表1的操作引发trigger1,grigger1对表1操作再次引发trigger1
--在默认的情况下，sql是禁止直接递归的，使用sql server开启直接递归，可以用以下方法：
--1:通过系统存储过程sp_dboption进行设置
--语法：
--sp_dboption 'dbname','recursive triggers',true
--recursive:递归的；循环的  dbname:为数据库名  true：开启   false：关闭
--2:通过企业管理理查看数据库属性，在选项标签的：递归触发器选择开启：


create table storage
(
编号 int,
物品名称 char(10),
库存  int,
单位  char(2)
)
--创建表
alter table storage alter column 编号 int not null
insert into storage  values(101,'洗衣粉',36,'袋')
insert into storage  values(102,'香皂',42,'盒')
insert into storage  values(103,'毛巾',36,'条')
insert into storage  values(104,'手套',36,'副')
insert into storage  values(201,'笔记本',39,'本')
insert into storage  values(202,'圆珠笔',36,'支')
insert into storage  values(103,'铅笔',36,'支')
select * from storage
--插入数据


create trigger sto_delete
on storage
for delete as 
declare @rowcount int
select @rowcount=@@rowcount
if @rowcount>1
begin
rollback transaction
raiserror('当前删除的记录数d%，一次只能删除一条数据',16,1,@rowcount)
end
if @rowcount=1
begin
declare @s_no int,
        @new_sno int
 select @s_no=编号
 from deleted
 select @new_sno=@s_no+1
 delete storage where 编号=@new_sno
 end
--创建递归删除触发器 
delete storage where 编号=101
select * from storage
sp_dboption 'seldata','recursive triggers',true
--设置打开数据库seldata中的递归调用


--SQL server触发器的管理
--1：sp_help  可以查看触发器的基本信息
sp_help sto_delete
--查看 sto_delete 触发器信息
--2：sp_helptext 查看触发器正文信息
sp_helptext sto_delete
--查看sto_delete触发器内容
--3：sp_depends 查看指定触发器引用的表
sp_depends student
--查看表引用的视图，存储过程 函数和触发器
sp_depends sto_delete
--触发器的类型