银行系统sql触发器代码

if exists (select * from sysdatabases where name ='BankCard')
drop database BankCard
go
create database BankCard/*创建数据库*/
use BankCard
create table Bank
(
CardID char(10) primary key,
BankName varchar(20)not null,
UserName varchar(20)not null,
Depisit money not null,
Payout money not null
)
alter  table Bank add constraint DF_Depisit default (0) for Depisit
alter table Bank add constraint DF_Payout default (0)for Payout

if exists (select * from sysobjects where name='trade')
drop table trade
create table Trade
(
TradeID int identity(1,1),
CardID char(10),
TradeDate DateTime  not null,
TradeMoney Money,
TradeType char
)
alter table Trade add constraint PK_TradeID primary key(TradeID),
alter table Trade add constraint FK_CardID foreign key (CardID) references Bank(CardID),
alter table Trade add constraint CK_TradeMoney check (TradeMoney>0)

/*触发器（trigger）*/
if exists (select * from sysobjects where name ='trig_Bank')
drop trigger trig_Bank
 
create trigger trig_Bank
on Trade /*trig_Bank为触发器的名称，Bank为表的名称*/
for  insert/*可以为inser、delete、select、update*/
as
declare @type char(4),@balance money,@outMoney money,@myCardID int
/*定义变量，用于临时存储
/*从临时表中获取插入的记录：类型、卡号、金额*/
select @type=TradeType,@outMoney=TradeMoney,@myCardID=CardID from  inserted
if(@type='支取')
update bank set Payout where

insert into trans(cardID,transType,transMoney)
values('10001000',支取,200)

if exists (select * from sysdatabases where name ='BankCard')
drop database BankCard
go
create database BankCard/*创建数据库*/
use BankCard

if exists (select * from sysobjects where name='Bank')
drop table Bank
create table Bank
(
CardID char(10) primary key,
BankName varchar(20)not null,
UserName varchar(20)not null,
Depisit money not null,
Payout money not null
)
alter  table Bank add constraint DF_Depisit default (0) for Depisit
alter table Bank add constraint DF_Payout default (0)for Payout

if exists (select * from sysobjects where name='trade')
drop table trade
create table Trade
(
TradeID int identity(1,1),
CardID char(10),
TradeDate DateTime  not null,
TradeMoney Money,
TradeType char (10)
)
alter table Trade add constraint PK_TradeID primary key(TradeID)
alter table Trade add constraint FK_CardID foreign key (CardID) references Bank(CardID)
alter table Trade add constraint CK_TradeMoney check (TradeMoney>0)

/*触发器（trigger）*/
if exists (select * from sysobjects where name ='trig_Bank')
drop trigger trig_Bank
 go
create trigger trig_Bank
on Trade /*trig_Bank为触发器的名称，Bank为表的名称*/
for  insert /*可以为inser、delete、select、update*/
as
--定义变量，用于临时存储
declare @type char(4)
declare @outMoney money
declare @balance money
declare @myCardID int
/*从临时表中获取插入的记录：类型、卡号、金额*/
select @type=TradeType,@outMoney=TradeMoney,@myCardID=CardID from  inserted
if(@type='支取')
update Bank set Payout where
go
select * from Bank
select *from trade

 

sa登陆失败

 

1、属性—安全性—将SQL server 改为 SQL server 和window server

 

2、打开安全性

双击登录
双击sa把密码改成sa