数据库实验触发器题目

/*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 TEASIF UPDA TE(balance)BEGINDECLARE @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) NULLCREA TE TRIGGER modify_loan ON loan AFTER UPDA TEASIF UPDA TE(amount)BEGINDECLARE @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,updateasdeclare @city char(30)declare @name char(15)select @city=(select branch_city from inserted)select @name=(select branch_name from inserted)if @city='NULL'beginupdate branch set branch_city='presently uncertain' where branch_name=@nameend/*验证*/insert into branch values('中国人民银行','null',122222)/*插入验证*/select * from branchinsert 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,updateasdeclare @city char(30)declare @name char(15)select @city=(select branch_city from inserted)select @name=(select branch_name from inserted)if @city='NULL'beginupdate branch set branch_city='unknow' where branch_name=@nameend/*验证*/insert into branch values('中国农业银行','null',11122222)/*插入验证*/select * from branchinsert into branch values('邮政储蓄银行','烟台',2881991991)update branch set branch_city='null' where branch_name='邮政储蓄银行'/*更新验证*/ select * from branch/*第二题仓库库存问题*********************************************/ create trigger reorder_trigger on inventory for updateas if update(levels)begindeclare @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 updateas 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)begininsert into loan select account_number,branch_name,-balancefrom account where account_number=@account_numinsert into borrower select * from depositor where account_number=@account_numupdate account set balance=0 where account_number=@account_numend/*检验*/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 loanselect * from account/*第四题:在对账号执行delete操作时,对账号的每一个拥有者,检查他是否有其他账户,如果没有,把他从depositor中删除*/create trigger dele_depositor_trigger on account instead of deleteasbegindeclare @account_num char(10)select @account_num=(select account_number from deleted)delete from depositor where account_number=@account_numdelete from depositor where account_number=@account_numend--验证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'。