CODEWITHSUNDEEP
sql
agni
agni

Reputation: 19

How to create trigger in sql that joins 2 tables

I need to create a trigger in sql that checks if the user has answered on question that related to a specific app is actually registered to that app.

The tables that I am using are:

1) tblAnsweredOn which contains the following columns:
email (Key), appName(key), questionNo(Key), answer, answerDate

2)tblRegisteredTo which contains the following columns:
email(key), appName(key), registrationDate, nickName

This is the code that i tried, but it not allows me to insert any user no matter if he is trying to answer on a question that related to an app he is registered to or not.

create trigger ifExistsTrigger
on [dbo].[tblAnsweredOn] after insert as
declare
@email nvarchar,
@appName nvarchar

select @email = INSERTED.email , @appName = INSERTED.AppName
from INSERTED

if not exists (select email, appName
               from [dbo].[tblRegisteredTo] 
               where email = @email
               and appName = @appName)
rollback;

Upvotes: 1

Views: 47

Answers (1)

zambonee
zambonee

Reputation: 1647

You are probably not getting the results you expect because you are treating the inserted pseudo-table as a scalar instead of as a table. You can simply join inserted and tblRegisteredTo to get your results.

IF EXISTS (
    SELECT email, appName
    FROM inserted A
    LEFT JOIN dbo.tblRegisteredTo B
    ON A.email = B.email AND A.appName = B.appName
    WHERE B.appName IS NULL OR B.email IS NULL)
ROLLBACK

Upvotes: 2

Related Questions