触发器

use demo_2;
go

--最简单的触发器，当向TB_Students表插入数据的时候，报告插入的记录
create trigger TG_STUDENTS_INSERT on TB_Students for insert as
begin
declare @stuId varchar(10);
declare @stuName nvarchar(20);

declare insertCursor cursor for select STU_ID,STU_NAME from inserted;
open insertCursor;

fetch insertCursor into @stuId,@stuName;

while(@@fetch_status = 0)
begin
print '学号: ' + @stuId + ' 姓名' + @stuName + '的学生被建立。';
fetch insertCursor into @stuId,@stuName;
end

close insertCursor;
deallocate insertCursor;
end
go

alter trigger TG_STUDENTS_UPDATE on TB_Students for Update as
begin
declare @oldStuId varchar(10);
declare @oldStuName nvarchar(20);
declare @oldStuGender nvarchar(1);
declare @oldStuBirthdate datetime;

declare @newStuId varchar(10);
declare @newStuName nvarchar(20);
declare @newStuGender nvarchar(1);
declare @newStuBirthdate datetime;

declare updateCursor cursor for select a.STU_ID as OLD_STU_ID, a.STU_NAME as OLD_STU_NAME,
a.STU_GENDER as OLD_STU_GENDER, a.STU_BIRTHDATE as OLD_STU_BIRTHDATE,
b.STU_ID as NEW_STU_ID, b.STU_NAME as NEW_STU_NAME, b.STU_GENDER as OLD_STU_GENDER,
b.STU_BIRTHDATE as NEW_STU_BIRTHDATE
from deleted as a inner join inserted as b on a.STU_KEY=b.STU_KEY;

open updateCursor;

fetch updateCursor into @oldStuId,@oldStuName, @oldStuGender, @oldStuBirthdate,
@newStuId, @newStuName, @newStuGender, @newStuBirthdate;

while(@@fetch_status = 0)
begin
print '学号:' + @oldStuId + ' 姓名' + @oldStuName
+ ' 性别:' + @oldStuGender + ' 生日:' + convert(nvarchar,@oldStuBirthdate,101)
+ '的学生被更改为 ' + '学号:' + @newStuId + ' 姓名' + @newStuName
+ ' 性别:' + @newStuGender + ' 生日:' + convert(nvarchar,@newStuBirthdate,101);

fetch updateCursor into @oldStuId,@oldStuName, @oldStuGender, @oldStuBirthdate,
@newStuId, @newStuName, @newStuGender, @newStuBirthdate;
end

close updateCursor;
deallocate updateCursor;
end
go

create trigger TG_STUDENTS_DELETE on TB_STUDENTS for delete as
begin
declare @stuId varchar(10);
declare @stuName nvarchar(20);

declare deleteCursor cursor for select STU_ID,STU_NAME from deleted;
open deleteCursor;

fetch deleteCursor into @stuId,@stuName;

while(@@fetch_status = 0)
begin
print '学号: ' + @stuId + ' 姓名' + @stuName + '的学生被删除。';
fetch deleteCursor into @stuId,@stuName;
end

close deleteCursor;
deallocate deleteCursor;
end

insert into TB_STUDENTS(STU_KEY,STU_ID,STU_NAME,STU_GENDER,CLS_ID,STU_BIRTHDATE)
values (newid(), '20090315','Tom','男', '77736c72-bbc6-4bdd-83d1-5fe3de1d886c','1990-05-23');

insert into TB_STUDENTS(STU_KEY,STU_ID,STU_NAME,STU_GENDER,CLS_ID,STU_BIRTHDATE)
values (newid(), '20090317','Snoopy','男', '77736c72-bbc6-4bdd-83d1-5fe3de1d886c','1990-05-23');

update TB_STUDENTS set STU_NAME='Tom Cruss', STU_BIRTHDATE='1988-02-14' where STU_NAME='Tom';
delete from TB_STUDENTS where STU_NAME='Jerry';

--利用触发器保证业务的完整性

use TRANS_DEMO;
go

create trigger TG_BANK_ACCOUNTS_UPDATE on BANK_ACCOUNTS for update as
begin
declare @accNum nvarchar(20);
declare @oldBalance float;
declare @newBalance float;
declare @desc nvarchar(50);

declare curBalance cursor for select a.ACC_NUM, a.BALANCE as OLD_BALANCE,
b.BALANCE as NEW_BALANCE from deleted as a
inner join inserted as b on a.ACC_NUM=b.ACC_NUM;

open curBalance;
fetch curBalance into @accNum, @oldBalance, @newBalance;

while(@@fetch_status=0)
begin
if(@newBalance > @oldBalance)
begin
set @desc = '存入';
end
else if(@newBalance < @oldBalance)
begin
set @desc = '扣除';
end
else
begin
fetch curBalance into @accNum, @oldBalance, @newBalance;
continue;
end

insert into OPER_LOGS (ACC_NUM,BALANCE_BEFORE,AMOUNT,BALANCE_AFTER,
OPER_DESC, OPER_TIME, OPERATOR) values
(@accNum, @oldBalance, @newBalance-@oldBalance, @newBalance,
@desc, GetDate(), System_User);

fetch curBalance into @accNum, @oldBalance, @newBalance;
end

close curBalance;
deallocate curBalance;
end

go

alter table OPER_LOGS alter column OPERATOR nvarchar(200) not null;
go

begin transaction
begin try
update BANK_ACCOUNTS set BALANCE=BALANCE-2000 where ACC_NUM='37432514332'
update BANK_ACCOUNTS set BALANCE=BALANCE+2000 where ACC_NUM='37432514357'
commit transaction
end try
begin catch
print Error_Message();
rollback transaction
end catch

--使用Instead Of触发器替换对于表中记录的删除操作

alter table BANK_ACCOUNTS add DEL bit not null default 0; --为帐户表增加一个删除标记字段
go

select * from BANK_ACCOUNTS;
go

alter trigger TG_BANK_ACCOUNTS_DELETE on BANK_ACCOUNTS instead of delete as
begin
update BANK_ACCOUNTS set DEL=1 where ACC_NUM in
(select ACC_NUM from deleted where DEL=0);

delete from BANK_ACCOUNTS where ACC_NUM in
(select ACC_NUM from deleted where DEL=1);
end

go

delete from BANK_ACCOUNTS where ACC_NUM='37432514332';
go

select * from BANK_ACCOUNTS;
go

--查询没有被删除的记录
select * from BANK_ACCOUNTS where DEL=0;

--查询已经被删除的记录
select * from BANK_ACCOUNTS where DEL=1;

--恢复已经被删除的记录
update BANK_ACCOUNTS set DEL=0 where ACC_NUM='37432514332';

--彻底删除已经被删除的记录
delete from BANK_ACCOUNTS where DEL=1; --删除失败，违反外键约束
go