数据库触发器的如何实现

创建一个AFTER INSERT触发器，实现输入的性别字段内容如果不是'男'或'女'时，不允许插入数据。

CREATE TABLE TestIdentity(
    ID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    Val int NOT NULL CHECK(Val >= 0))
GO

CREATE TRIGGER TR_TestIdentity_Insert
ON TestIdentity
INSTEAD OF INSERT
AS
    --只插入符合条件的数据
    INSERT INTO TestIdentity(Val)
    SELECT Val
    FROM inserted
    WHERE Val >= 0
GO

--测试插入错误数据
INSERT INTO TestIdentity VALUES(1)
INSERT INTO TestIdentity VALUES(2)
INSERT INTO TestIdentity VALUES(3)
INSERT INTO TestIdentity VALUES(NULL)
INSERT INTO TestIdentity VALUES(5)
INSERT INTO TestIdentity VALUES(-1)
INSERT INTO TestIdentity VALUES(7)

可以仿照这个写。

CREATE TABLE TestIdentity(
    ID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    Val nvarchar(2) NOT NULL CHECK(Val ='男' or Val ='女'))
GO
 
CREATE TRIGGER TR_TestIdentity_Insert
ON TestIdentity
INSTEAD OF INSERT
AS
    --只插入符合条件的数据
    INSERT INTO TestIdentity(Val)
    SELECT Val
    FROM inserted
    WHERE Val ='男' or Val ='女'
GO
 
--测试插入错误数据
INSERT INTO TestIdentity VALUES('2')
INSERT INTO TestIdentity VALUES('男')
INSERT INTO TestIdentity VALUES('女')
INSERT INTO TestIdentity VALUES(NULL)
INSERT INTO TestIdentity VALUES(5)
INSERT INTO TestIdentity VALUES(-1)