数据库实验触发器题目

/*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**********/