57 lines
1.6 KiB
Transact-SQL
57 lines
1.6 KiB
Transact-SQL
USE Homework250417
|
||
Go
|
||
/* 某公司想举行一个小型的联谊会，关系Male记录注册的男宾信息，关系Female记录注册的女宾信息。
|
||
建立一个触发器，将来宾的人数现在在50人以内。 */
|
||
CREATE TABLE Male(
|
||
ID INT PRIMARY KEY IDENTITY(1,1),
|
||
Name NVARCHAR(20) NOT NULL,
|
||
);
|
||
CREATE TABLE Female(
|
||
ID INT PRIMARY KEY IDENTITY(1,1),
|
||
Name NVARCHAR(20) NOT NULL,
|
||
);
|
||
Go
|
||
CREATE TRIGGER trg_LimitGuestCount_Male
|
||
ON Male
|
||
INSTEAD OF INSERT
|
||
AS
|
||
BEGIN
|
||
DECLARE @MaleCount INT;
|
||
DECLARE @FemaleCount INT;
|
||
DECLARE @TotalCount INT;
|
||
SELECT @MaleCount = COUNT(*) FROM Male;
|
||
SELECT @FemaleCount = COUNT(*) FROM Female;
|
||
SET @TotalCount = @MaleCount + @FemaleCount;
|
||
IF @TotalCount >= 50
|
||
RAISERROR(N'宾客人数已满，无法添加更多男宾', 16, 1)
|
||
ELSE
|
||
BEGIN
|
||
INSERT INTO Male (Name)
|
||
SELECT Name FROM INSERTED;
|
||
PRINT(N'添加男宾信息成功');
|
||
END
|
||
END;
|
||
Go
|
||
CREATE TRIGGER trg_LimitGuestCount_Female
|
||
ON Female
|
||
INSTEAD OF INSERT
|
||
AS
|
||
BEGIN
|
||
DECLARE @MaleCount INT;
|
||
DECLARE @FemaleCount INT;
|
||
DECLARE @TotalCount INT;
|
||
SELECT @MaleCount = COUNT(*) FROM Male;
|
||
SELECT @FemaleCount = COUNT(*) FROM Female;
|
||
SET @TotalCount = @MaleCount + @FemaleCount;
|
||
IF @TotalCount >= 50
|
||
RAISERROR(N'宾客人数已满，无法添加更多女宾', 16, 1)
|
||
ELSE
|
||
BEGIN
|
||
INSERT INTO Female (Name)
|
||
SELECT Name FROM INSERTED;
|
||
PRINT(N'添加女宾信息成功');
|
||
END
|
||
END;
|
||
Go
|
||
-- Test
|