>我正在尝试创建一个"而不是插入触发器",它不会让名称"john"将任何内容插入表中。 我的问题是,即使我将名称更改为其他名称,查询也会成功,但不会添加值。
任何帮助将不胜感激,提前感谢。
CREATE TRIGGER InsteadOfTrigger
ON Question4
INSTEAD OF INSERT
AS
Declare @name varchar(50)
Declare @question varchar(50)
Declare @Answer char
Set @name = 'John'
IF (select Username from inserted) = @name
BEGIN
RAISERROR ('You have not paid up your fee', 10,1)
ROLLBACK TRANSACTION
END
ELSE
BEGIN
INSERT INTO question4
values (@name, @question, @Answer)
END
好的,
所以我删除了您的IF ELSE
语句之间的BEGIN
和END
语句,并将触发器逻辑包装在BEGIN END
如下面的评论中所述,您不需要ROLLBACK TRANSACTION
此外,您还需要填充@question和@Answer,以便这些有用。
CREATE TRIGGER InsteadOfTrigger
ON Question4
INSTEAD OF INSERT
AS
BEGIN
Declare @name varchar(50)
Declare @question varchar(50)
Declare @Answer char
Set @name = 'John'
IF (select Username from inserted) = @name
RAISERROR ('You have not paid up your fee', 10,1)
--ROLLBACK TRANSACTION
ELSE
INSERT INTO question4
values (@name, @question, @Answer)
END
嗯,我注意到您已经声明了,但实际上并没有在 else 语句中为您的变量设置值,这可能导致 SQL 无法插入您期望的内容。奇怪的是,我现在需要在作业中做同样的事情,这是我的解决方案:
CREATE TRIGGER instead_of_insert
ON Question4
INSTEAD OF INSERT AS
Declare @Username varchar(25)
Declare @Question varchar(6)
Declare @Answer char(1)
Set @Username ='John'
IF (Select UserName from inserted) = @UserName
Begin
RAISERROR ('You have not paid up your fee', 10,1)
End
Else
Begin
Set @Username = (Select UserName from inserted)
Set @Question = (Select Question_ID from inserted)
Set @Answer = (Select Answer from inserted)
Insert into User_Responses
Values
(@username, @Question, @Answer)
End