On the sc table create two trigger sc_ai and sc_au, when insert or update the data after the data, if someone take more courses than 2 door, just interrupt operation and automatic transaction rollback, strives for the bosses tell me something about how to interrupt operation?

CodePudding user response:

 USE tempdb for 
GO 
IF OBJECT_ID (' sc ') IS NOT NULL 
DROP TABLE sc 
GO 
- schedule, for the sake of simplicity in name and course name as logo, 
- actual application should be student id number and course 
The CREATE TABLE sc (
ScId INT PRIMARY KEY, 
StuName NVARCHAR (10), 
CourseName NVARCHAR (10), 
Score INT 
) 
GO 
SET NOCOUNT ON 
INSERT INTO sc (scId, stuName, courseName, score) VALUES (1, 'xiao Ming', 'language', 88) 
INSERT INTO sc (scId, stuName, courseName, score) VALUES (2, 'xiao Ming', 'mathematics', 68) 
INSERT INTO sc (scId, stuName, courseName, score) VALUES (3, 'the little red', 'language', 76) 
INSERT INTO sc (scId, stuName, courseName, score) VALUES (4, 'liu', 'English', 91) 
GO 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- above for testing table and test data -- -- -- -- -- -- -- -- -- -- -- -- -- 

-- -- -- -- -- -- -- -- -- -- -- -- -- the trigger does not need two, one is enough -- -- -- -- -- -- 
-=============================================
Author: yenange 
- the Create date: 2019-10-7 
- Description: after the insert or update the data after the data, 
- more than 2 doors, if someone is optional courses will interrupt operation and automatic transaction rollback 
-=============================================
CREATE the TRIGGER trig_sc_I_U 
ON dbo. Sc 
AFTER INSERT, UPDATE 
AS 
The BEGIN 
SET NOCOUNT ON; 
IF (
SELECT COUNT (1) the FROM 
(
The SELECT sc stuName, sc. CourseName FROM sc INNER JOIN INSERTED ON sc. StuName=INSERTED. StuName 
EXCEPT, 
The SELECT stuName, courseName FROM does 
The UNION 
The SELECT stuName, courseName FROM INSERTED 
) AS t 
) & gt; 2 
The BEGIN 
The ROLLBACK TRAN. 
RAISERROR (' elective courses can not more than 2 doors, 16, 1); 
END 
END 
GO 

- xiao Ming's mathematics to English 
The UPDATE sc SET courseName='English' WHERE scId=2 
GO 
- and then insert a xiao Ming's record 
INSERT INTO sc (scId, stuName, courseName, score) VALUES (5, 'xiao Ming', 'history', 68) 
GO 
/* 
Message 50000, level 16, state 1, process trig_sc_I_U, line 27 [batch starting line 50] 
Elective courses can not more than 2 door 
*/
- insert a record of xiaoliu 
INSERT INTO sc (scId, stuName, courseName, score) VALUES (6, 'liu', 'history', 53) 
GO 

SELECT * FROM sc 
/* 
ScId stuName courseName score 
Xiao Ming Chinese 88 
 12 xiao Ming English 68 
Three little red Chinese 76 
4 liu English 91 
6 liu history 53 
*/