触发器

--数据库表名

CREATE TABLE BANKUSER
(
BID INT PRIMARY KEY IDENTITY,
BNUM NVARCHAR(50),
BNAME NVARCHAR(50) ,
BPWD NVARCHAR(50),
BMONEY DECIMAL
)

--数据库表名

CREATE TABLE BANKLOGS
(
LID INT PRIMARY KEY IDENTITY,
BID INT,
BNUM NVARCHAR(50),
BNAME NVARCHAR(50) ,
BT NVARCHAR(50),
BMONEY DECIMAL

)

 

--创建insert触发器
create trigger trig_insert
on users--添加的数据库表
after insert
as
begin
declare @name varchar(20), @hao int, @num int;
select @name=name from inserted;
select @hao=hao from inserted;
select @num=num from inserted;
insert into yang values(@name,@hao,@num)--触发的数据库表
end
---删除
alter trigger trig_inserts
on users
after delete
as
begin
if object_id(N'yang',N'U') is null--判断BANKLOGS表是否存在
CREATE TABLE yang
(
id int primary key identity,
name nvarchar(50),
hao nvarchar(50),
num int

);--创建记录表
declare @id int;
select id as 已删除的学生编号, name, hao,num
from deleted
--delete from yang where id=@id

end

----- 添加

create trigger trig_stus
on BANKUSER
after insert
as
begin
if object_id(N'BANKLOGS',N'U') is null--判断BANKLOGS表是否存在
CREATE TABLE BANKLOGS
(
LID INT PRIMARY KEY IDENTITY,
BID INT,
BNUM NVARCHAR(50),
BNAME NVARCHAR(50) ,
BT NVARCHAR(50),
BMONEY DECIMAL

);--创建记录表
insert into BANKLOGS (BNUM,bid,BNAME,BT,BMONEY)
select BNUM,bid,BNAME,1,BMONEY from inserted
end

insert into BANKUSER (BNUM ,BNAME,BMONEY)
values ('001','小杨',1000000)
select * from BANKUSER
select * from BANKLOGS
---修改
create trigger trig_sss
on BANKUSER
after update
as
begin
if object_id(N'BANKLOGS',N'U') is null--判断BANKLOGS表是否存在
CREATE TABLE BANKLOGS
(
LID INT PRIMARY KEY IDENTITY,
BID INT,
BNUM NVARCHAR(50),
BNAME NVARCHAR(50) ,
BT NVARCHAR(50),
BMONEY DECIMAL

);--创建记录表
declare @bmoney_new DECIMAL
declare @bmoney_old DECIMAL
select @bmoney_new=BMONEY from inserted
select @bmoney_old=BMONEY from deleted
if @bmoney_old>=@bmoney_new
begin
if(@bmoney_new>=0)
begin
insert into BANKLOGS (BNUM,bid,BNAME,BT,BMONEY)
select BNUM ,bid,BNAME,2,BMONEY from deleted
end
else
begin
print '账户余额不足!'
rollback tran
end
end
else
begin
insert into BANKLOGS (BNUM,bid,BNAME,BT,BMONEY)
select BNUM ,bid,BNAME,3,BMONEY from deleted
end

end

 

update BANKUSER set BMONEY=BMONEY-1000000 where BNUM='001'

update BANKUSER set BMONEY=BMONEY+2000000 where BNUM='001'

转载于:https://www.cnblogs.com/net520/p/7860744.html