排除某些字词的触发器

Question

我有一个与 T-SQL 中的数据库触发器相关的问题。

我想创建一个触发器，该触发器仅允许特定单词(可用，不可用(到列avalability。

怎么做？

我了解触发器是什么，但在命令序列中丢失了 - 应该在什么之后出现什么。

Answer 1

不确定这里是否真的需要触发器。另一种解决方案是在相关列上创建一个CHECK约束，例如：

ALTER TABLE TableName
ADD CONSTRAINT availability_check CHECK (availability IN ('available', 'not available'));

DB小提琴上的演示：

CREATE TABLE TableName (availability VARCHAR(20) );
ALTER TABLE TableName
ADD CONSTRAINT availability_check CHECK (availability IN ('available', 'not available'));
INSERT INTO TableName(availability) VALUES('foo');
-- Error
Msg 547 Level 16 State 0 Line 1
The INSERT statement conflicted with the CHECK constraint "availability_check". The conflict occurred in database "fiddle_325b8b256056466caf52aa4bb7467fd7", table "dbo.TableName", column 'availability'.
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.

Answer 2

您可以ALTER表并直接添加CHECK Constraint

ALTER TABLE YourTableName
ADD CONSTRAINT CHK_Avalability CHECK(Avalability IN('Available', 'Not Available'));

请注意，如果表中已插入一些数据并且与CHECK Constraint不匹配和冲突，这将引发错误。

如果您还没有CREATE您的桌子，那么您可以使用

CREATE TABLE YourTableName(
Availability VARCHAR(20) NOT NULL
CONSTRAINT CHK_Avalability CHECK(Availability IN('Available', 'Not Available')) 
);

Answer 3

有两种方法可以解决这个问题。

1. 选择：基于检查约束的方法：您只需使用检查约束即可实现它。它使工作更简单，正如@GMB在他的回答中已经提到的那样。

ALTER TABLE Table1 ADD CONSTRAINT CHK_Table1_Availability CHECK (Availability IN ('Available', 'Not Available'))

2. 不要选择：基于触发器的方法：如果要创建触发器，可以创建INSTEAD INSTEAD TRIGGER，如下所示。此外，还应创建类似的"而不是更新"触发器，以处理更新方案。但是，这是一种迂回的方法。查看所需的代码量，相应的维护。

CREATE TRIGGER [dbo].[tr_availability_insert]
ON [dbo].[Table1]
INSTEAD OF INSERT
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
SET NOCOUNT ON;
IF (inserted.Availability IN ('available', 'not available'))
BEGIN 
INSERT INTO Table1(Id, Availability)
SELECT Id, Availability FROM inserted
END
COMMIT;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
RETURN;
END CATCH

CREATE TRIGGER [dbo].[tr_availability_update]
ON [dbo].[Table1]
INSTEAD OF UPDATE
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
SET NOCOUNT ON;
IF (inserted.Availability IN ('available', 'not available'))
BEGIN 
UPDATE Table1
SET Availability = inserted.Availability
WHERE Table1.Id = inserted.Id;
END
COMMIT;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
RETURN;
END CATCH

Answer 4

CHECK 约束是我会走的路。另一种方法是使用引用约束，即主键和外键。如果在多个位置使用"可用"和"不可用"，这将比 CHECK 约束更好，因为您可以在多个表中强制实施相同的规则。通过这种方式，您可以强制实施一致性，并避免某些表具有其他值，如"可用"和"不可用"。

-- Lookup table for all possible "Availability" values
CREATE TABLE dbo.LU_Availability
(
AvailabilityPK VARCHAR(14) NOT NULL,
CONSTRAINT pk_LU_Availability PRIMARY KEY CLUSTERED(AvailabilityPK)
);
INSERT dbo.LU_Availability (AvailabilityPK) VALUES ('Available'),('Not Available');
-- Table with the allowable values enforced via foriegn key constraint
CREATE TABLE dbo.YourTable
(
someId         INT IDENTITY,
someValue      CHAR(10),
[Availability] VARCHAR(14) NOT NULL,
CONSTRAINT fk_AvailabilityTxt FOREIGN KEY([Availability]) 
REFERENCES dbo.LU_Availability(AvailabilityPK)
);
INSERT dbo.YourTable ([Availability]) VALUES ('Available');     -- Succeeds
INSERT dbo.YourTable ([Availability]) VALUES ('NOT Available'); -- Succeeds
INSERT dbo.YourTable ([Availability]) VALUES ('Perhaps');       -- Fails

最后 - 重要的是要理解，虽然约束(CHECK，PK，FK，UNIQUE(会稍微减慢速度，但触发器是性能杀手。这就是为什么经验丰富的开发人员和 DBA 建议在约束可以解决问题时避免使用它们的原因。