sql 触发器（trigger）_sql trigger hive-优快云博客

本文介绍了一个银行交易系统的SQL触发器实现细节，包括插入、删除及更新操作对应的触发器设计，并展示了如何通过触发器来更新银行账户余额及备份删除的数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--------------------------------
---------Trigger-----------------
--------------------------------
use stuDB
--创建bank表
if exists (select * from sysobjects where name='bank')
 drop table bank
go 
create table bank
(
 cardID char(10) not null,
 customerName char(10) not null,
 currentMoney money not null
)
go
--创建transInfo表
if exists (select * from sysobjects where name='transInfo')
 drop table transInfo
go
create table transInfo
(
 cardID char(10) not null,
 transType char(4) not null, 
 transMoney money not null,
 transDate datetime not null
)
go
--添加约束
alter table transInfo
 add constraint df_transDate default (getDate()) for transDate
go
--给bank添加数据
insert into bank (cardID,customerName,currentMoney) values ('1001001','张三',1)
insert into bank (cardID,customerName,currentMoney) values ('1001002','李四',1000)
 
--insert 触发器
select * from bank
if exists (select * from sysobjects where name='trig_insert')
 drop trigger trig_insert
go
create trigger trig_insert
 on transInfo
  for insert
   as
   declare @type char(4)
   declare @money money
   declare @id char(10) 
   print '交易之前:'
   select * from bank
   select @type=transType,@money=transMoney,@id=cardID from inserted
   if(@type='支出')
    begin
     update bank set currentMoney=currentMoney-@money where cardID=@id
    end
   else
    begin
     update bank set currentMoney=currentMoney+@money where cardID=@id
    end
   print '交易之后'
   select * from bank
--测试触发器
insert into transInfo(cardID,transMoney,transType) values('1001001',80,'存入')
insert into transInfo(cardID,transMoney,transType) values('1001002',80,'支出')

--delete 触发器
select * from transInfo
if exists (select * from sysobjects where name='trig_delete')
 drop trigger trig_delete
go
create trigger trig_delete
 on transInfo
  for delete
   as
   if not exists (select * from sysobjects where name='backupTable')
    select * into backupTable from deleted 
   else
    insert into backupTable select * from deleted
   print 'backupTable'
   select * from backupTable
delete transInfo
--update 触发器
if exists (select * from sysobjects where name='trig_update')
 drop trigger trig_update
go
create trigger trig_update
 on bank
  for update
   as
   declare @beforeMoney money,@afterMoney money
   select @beforeMoney=currentMoney from deleted
   select @afterMoney=currentMoney from inserted
   print '交易前:'+convert(varchar(10),@beforeMoney)
   print '交易后:'+convert(varchar(10),@afterMoney)
go
update bank set currentMoney=currentMoney-10 where cardID='1001001'
go