数据库实验习题2.4.sql

数据库第二章习题程序

/*USE SCHOOL
GO
CREATE TRIGGER T1 ON WORKER
FOR INSERT,UPDATE
AS
IF(SELECT SAGE FROM INSERTED)<1
BEGIN
PRINT 'SAGE MUST BE A INTEGER MORE THAN ZERO! TRANSACTION FAIL'
ROLLBACK TRANSACTION
END*/

/*USE SCHOOL
GO
CREATE TRIGGER T2 ON WORKER
FOR DELETE
AS
IF(SELECT NUMBER FROM DELETED)='00001'
BEGIN
PRINT 'HE IS THE CEO! DELETE FAIL!'
ROLLBACK TRANSACTION
END*/

/*USE SCHOOL
GO
CREATE TRIGGER T3 ON WORKER
FOR UPDATE
AS 
IF UPDATE(NUMBER)
BEGIN 
PRINT 'EVERYNUMBER CANNOT BE CHANGED!'
ROLLBACK TRANSACTION
END*/

/*USE SCHOOL
UPDATE WORKER SET SAGE=-7 WHERE NUMBER='00001'*/

/*USE SCHOOL
DELETE FROM WORKER WHERE NAME='YongLi'*/

/*USE SCHOOL
UPDATE WORKER SET NUMBER='00007' WHERE SEX='FAMALE'*/

/*(1)
USE SCHOOL
GO
CREATE TRIGGER T4 ON WORKER
FOR INSERT
AS 
IF(SELECT SAGE FROM INSERTED)<=(SELECT MAX(SAGE) FROM WORKER)
BEGIN
PRINT 'THE SAGE OF COUPLE MUST BE MORE THAN THE EXISTED COUPLES''SAGE!';
ROLLBACK TRANSACTION
END*/

/*(2)
USE SCHOOL
GO
CREATE TRIGGER T5 ON WORKER
FOR UPDATE
AS
IF(SELECT SAGE FROM INSERTED)<=(SELECT SAGE FROM DELETED)
BEGIN
PRINT 'THE SAGE OF NEW COUPLE MUST BE MORE THAN THE SAGE OF OLD COUPLE!';
ROLLBACK TRANSACTION
END*/