数据库实验触发器题目

/*1一个用于跟踪哪个用户、何时间更新了Account表的balance*/

create table trigger_account

(

account_number char(10),

branch_name char(15),

userOfUpdate char(16) null,

timeOfUpdate DA TETIME NULL,

balance_old numeric(12,2) NULL,

balance_new numeric(12,2) NULL

)

CREA TE TRIGGER modify_account ON account AFTER UPDA TE

AS

IF UPDA TE(balance)

BEGIN

DECLARE @balance_newnumeric(12,2)

DECLARE @balance_oldnumeric(12,2)

DECLARE @account_numberchar(10)

DECLARE @branch_namechar(15)

SELECT @balance_old=(SELECT balance FROM deleted)

SELECT @balance_new=(SELECT balance FROM inserted)

SELECT @account_number=(SELECT account_number FROM deleted)

SELECT @branch_name=(SELECT branch_name FROM deleted)

INSERT INTO trigger_accountV ALUES(@account_number,@branch_name,USER_NAME(),GETDA TE(),@bal ance_old,@balance_new)

END

/*检验*/

--插入数据

insert into branch values('建设银行','烟台',1883333338)

insert into account values('22222222','建设银行',100000)

--检验

update account set balance=150000 where account_number='22222222'

select * from trigger_account

/*2.另一个用于跟踪哪个用户、何时间更新了loan表的amount*/

create table trigger_loan

(

loan_number char(10),

branch_name char(15),

userOfUpdate char(16) null,

timeOfUpdate DA TETIME NULL,

amount_old numeric(12,2) NULL,

amount_new numeric(12,2) NULL

CREA TE TRIGGER modify_loan ON loan AFTER UPDA TE

AS

IF UPDA TE(amount)

BEGIN

DECLARE @amount_newnumeric(12,2)

DECLARE @amount_oldnumeric(12,2)

DECLARE @loan_numberchar(10)

DECLARE @branch_namechar(15)

SELECT @loan_number=(SELECT loan_number FROM deleted)

SELECT @amount_old=(SELECT amount FROM deleted)

SELECT @amount_new=(SELECT amount FROM inserted)

INSERT INTO trigger_loanV ALUES(@loan_number,@branch_name,USER_NAME(),GETDA TE(),@amount_ol d,@amount_new)

END

/*检验*/

--插入数据

insert into branch values('烟台银行','烟台',100000000)

insert into loan values('11111111','烟台银行',100000)

--检验

update loan set amount=150000 where loan_number='11111111'

select * from trigger_loan

/*3.当Branch表的city为NULL时，一律写成“presently uncertain”*/

create trigger city_null on branch for insert,update

as

declare @city char(30)

declare @name char(15)

select @city=(select branch_city from inserted)

select @name=(select branch_name from inserted)

if @city='NULL'

begin

update branch set branch_city='presently uncertain' where branch_name=@name

end

/*验证*/

insert into branch values('中国人民银行','null',122222)/*插入验证*/

select * from branch

insert into branch values('中国华夏银行','烟台',2881991991)

update branch set brach_city='null' where branch_name='中国华夏银行'/*更新验证*/

select * from branch

/*************************课本作业************************************************************/

/*************第一题：当branch中branch_city为null时更名为unknow**********/

create trigger cityNull1 on branch for insert,update

as

declare @city char(30)

declare @name char(15)

select @city=(select branch_city from inserted)

select @name=(select branch_name from inserted)

if @city='NULL'

begin

update branch set branch_city='unknow' where branch_name=@name

end

/*验证*/

insert into branch values('中国农业银行','null',11122222)/*插入验证*/

select * from branch

insert into branch values('邮政储蓄银行','烟台',2881991991)

update branch set branch_city='null' where branch_name='邮政储蓄银行'/*更新验证*/ select * from branch

/*第二题仓库库存问题*********************************************/ create trigger reorder_trigger on inventory for update

as if update(levels)

begin

declare @level_old numeric(12,2)

declare @level_new numeric(12,2)

declare @item char(10)

declare @amount numeric(12,2)

select @level_old=(select levels from deleted)

select @level_new=(select levels from inserted)

select @item=(select item from deleted)

select @amount=(select amount from reorder where reorder.item=@item)

if (@level_new<=(select levels from minlevel where minlevel.item=@item)

and @level_old>(select levels from minlevel where minlevel.item=@item) )

insert into orders values(@item,@amount)

end

/*验证*/

insert into inventory values('原料x',60)

insert into minlevel values('原料x',50)

insert into reorder values('原料x',100)

update inventory set levels=20 where item='原料x'

select * from orders

/*第三题:银行处理透支情况*********************/

create trigger overdraft_trigger on account for update

as if update(balance)

declare @account_num char(10)

declare @balance_new numeric(12,2)

select @account_num=(select account_number from deleted)

select @balance_new=(select balance from inserted)

if(@balance_new<0)

begin

insert into loan select account_number,branch_name,-balance

from account where account_number=@account_num

insert into borrower select * from depositor where account_number=@account_num

update account set balance=0 where account_number=@account_num

end

/*检验*/

insert into account values('1111110','民生银行',1000)

insert into customer values('Jom','烟台','南大街')

insert into branch values('民生银行','烟台','1000000000')

insert into depositor values('Jom','1111110')

update account set balance=-1000 where account_number='1111110'

select * from loan

select * from account

/*第四题:在对账号执行delete操作时,对账号的每一个拥有者,检查他是否有其他账户,如果没有,把他从depositor中删除*/

create trigger dele_depositor_trigger on account instead of delete

as

begin

declare @account_num char(10)

select @account_num=(select account_number from deleted)

delete from depositor where account_number=@account_num

delete from depositor where account_number=@account_num

end

--验证

insert into account values('1111110','民生银行',1000)

insert into customer values('Join','烟台','南大街')

insert into account values('12222222', '民生银行',10000)

insert into account values('13333333', '民生银行',10000)

insert into depositor values('Join','12222222' )

insert into depositor values('Join','13333333' )

insert into customer values('Bob','烟台','南大街')

insert into account values('4444444444', '民生银行',10000)

insert into depositor values('Bob','4444444444' )

delete from account where account_number='12222222'

delete from account where account_number='4444444444'