SQL server 触发器相关理解（经常忘记）

主要接触的触发器为DML数据操作语言，涉及到增删改，主要操作inserted表和delete的表，个人理解，当你需要提示新的信息的时候查询inserted表，当然了查询修改前和删除之后提示信息就用deleted

create database pho
go
use pho
go
create table phone
(
phoneNo    varchar(20) primary key check(len(phoneNo) =11)   ,--   手机号码,主键
realName    varchar(20)    ,--  真是姓名
idcard      varchar(18)    ,--  身份证,
balance     numeric(6,2)    --   余额
)
--充值记录表phoneLog
create table phoneLog
(
logID      int   primary key  ,  --     主键  日志ID
phoneNo   varchar(20) references phone(phoneNo) ,  --    手机号  外键
logDate    datetime    , --       日志日期
remark     varchar(255) --     日志说明
)

------------------------------------------------
--1、创建表

--2、给phoneLog 表主键logID 添加自动增长触发器
   
   if exists(select 1 from sys.triggers where name='tr_insert_Log')
   drop trigger tr_insert_Log
   go
   create trigger tr_insert_Log
   on phoneLog
   instead of insert
   as
   --定义一个变量来接受logID的最大值
   declare @max_id int 
   declare @phoneNo varchar(11) --接受phone表的phoneNo
   declare @remark varchar(50) --
   --赋值
   select @max_id=max(logID)from inserted
   --判断@max_id
   if(@max_id is null)
   begin
    select @max_id=1
   end
   else
   begin
    select @max_id+=1
   end
   --添加数据
   select @phoneNo=phoneNo,@remark=remark from inserted
   insert into phoneLog values(@max_id,@phoneNo,getdate(),@remark)
go
   
--3、触发器： 新开一个手机账号，并在充值记录表中 保存日志说明为 ‘开户’
if exists (select 1 from sys.triggers where name='tr_insert_phone')
drop trigger tr_insert_phone
go
create trigger tr_isnert_phone
on phone
for insert 
as
declare @phoneNo varchar(20)
select @phoneNo =phoneNo from inserted
insert into phoneLog values(null,@phoneNo,getdate(),'开户')
go
insert into phone values('13271862151','张三','511324564545454',10)
go
select * from phone
select * from phoneLog
--4、触发器：给手机充值时，保存充值记录，并在日志说明充值多少钱，手机总共余额多少钱
if exists(select 1 from sys.triggers where name ='tr_update_phone')
drop trigger tr_update_phone
go
create trigger tr_update_phone
on phone
for update
as
declare @phoneNo varchar(20),@balance money
declare @balance1 money--修改后de
declare @balance2 money --修改前的钱
select @phoneNo=phoneNo,@balance1=balance from inserted
select @balance=balance from deleted
update phoneLog set remark='充值了'+convert(varchar,@balance1-@balance2) where phoneNo=@phoneNo
print '手机总共余额是：'+convert(varchar,@balance1)
go
update phone set balance+=50 where phoneNo='13271862151'
go

--5、触发器： 删除充值记录时，显示当前手机账户的真实姓名，和身份证号码
if exists(select 1 from sys.triggers where name ='tr_delete_phoneLog')
drop trigger tr_delete_phoneLog
go
create trigger tr_delete_phoneLog
on phoneLog
for delete
as
declare @name varchar(20),@number varchar(18),@phoneNo varchar(20)
select @phoneNo =phoneNo from deleted
delete from phoneLog where phoneNo=@phoneNo
select @name =realName,@number=idcard from phone where phoneNo=@phoneNo
print '姓名：'+@name+',身份证号：'+@number
go
delete from phoneLog where phoneNo='13271862151'
go
--6、触发器：删除手机表中手机号时，自动删除充值记录中的所有记录
if exists(select 1 from sys.triggers where name ='tr_delete_phone')
drop trigger tr_delete_phone
go
create trigger tr_delete_phone
on phone
instead of delete
as
declare @phoneNo varchar(20)
select @phoneNo=phoneNo from deleted
delete from phoneLog where phoneNo=@phoneNo
delete from phone where phoneNo=@phoneNo
go
delete from phone where phoneNo='13271862151'
go