CODEWITHSUNDEEP
sql-servert-sql
Hursh Ramchurn
Hursh Ramchurn

Reputation: 43

Instead of insert trigger SQL

I am trying to create an 'instead of insert trigger' that will not let the name 'john' insert anything into a table. My problem is that even if i change the name to something else, the query is successful but the values arent 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

Upvotes: 4

Views: 3361

Answers (2)

Muz
Muz

Reputation: 39

Hmm, I notice you have declared, but not actually set a value for your variables in your else statement this may have caused SQL to not insert what you expected. Strangely enough I'm required to do the same in an assignment at the moment, Here's 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

Upvotes: 2

Squirrel5853
Squirrel5853

Reputation: 2406

Ok So I have removed your BEGIN and END statements between your IF ELSE statement and wrapped the trigger logic within a BEGIN END

As mentioned in the comments below you dont need the ROLLBACK TRANSACTION

Also you will need to populate @question and @Answer for those to be of any use.

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

Upvotes: 4

Related Questions