Instead of inserting trigger SQL

Question

I am trying to create a "instead of an insert trigger" that will not allow the name "john" to insert anything into the table. My problem is that even if I change the name to something else, the request will succeed, but the arent value is added.

Any help would be appreciated, thanks in advance.

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 

Answer 1

Ok So, I deleted your BEGIN and END statements between your IF ELSE and wrapped the trigger logic in BEGIN END

As mentioned in the comments below, you do not need ROLLBACK TRANSACTION

You will also need to fill out @question and @Answer for those who will use it.

 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 

Answer 2

Hmm, I notice that you stated, but did not actually set a value for your variables in your else statement, this could cause SQL not to insert what you expected. Oddly enough, I need to do the same in the task at the moment, here is my solution:

 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