CODEWITHSUNDEEP
sqlsql-server
Kram
Kram

Reputation: 526

Add trigger before insert SQL Server

I would like to create a trigger that prevents from 2 users with the same nickname to registered to the same app.

For example, if I have user Bob with nickName B123 that registered to app1 I don't want that user Brian with nick B123 will be able to register for app1.

The table that I'm using is tblRegisteredTo with these columns:

email (key), appName (key), registrationDate, nickName

This is what I tried to do, but it won't let me register any user no matter if he has the same nickname or not.

CREATE TRIGGER ifSameNick
ON [dbo].[tblRegisteredTo] 
AFTER INSERT
AS
    DECLARE @email NVARCHAR(100),
            @appName NVARCHAR(50),
            @nickName NVARCHAR(55)

    SELECT
        @email = INSERTED.email, 
        @appName = INSERTED.AppName, 
        @nickName = INSERTED.nickName
    FROM
        INSERTED

    IF EXISTS (SELECT * FROM dbo.tblRegisteredTo B
               WHERE B.nickName = @nickName AND B.appName = @appName)
        ROLLBACK

Upvotes: 0

Views: 3600

Answers (2)

zambonee
zambonee

Reputation: 1647

Gordon's answer is right, but if you MUST use a trigger, you have to account for the records that you just inserted (expecting no more than 1 row with that nickName and appName). Also, don't treat the inserted pseudo-table as a scalar.

create trigger ifSameNick
on [dbo].[tblRegisteredTo] after insert as

IF EXISTS (
    SELECT * FROM dbo.tblRegisteredTo B
    INNER JOIN inserted ON B.nickName = inserted.nickName and B.appName = inserted.appName
    GROUP BY inserted.nickName, inserted.appName
    HAVING COUNT(*) > 1)
ROLLBACK

In writing this, I have gotten a new-found appreciation for the existence of the UNIQUE constraint.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269603

You should not do this with a trigger. You should do this with a unique constraint:

alter table tblRegisteredTo
    add constraint unq_tblRegisteredTo_nickname
        unique (nickname);

If you are learning SQL and this is an example of a trigger, then it is a really bad example. You should learn how to use a database correctly.

Upvotes: 2

Related Questions