CODEWITHSUNDEEP
sqlsql-serverif-statementtriggers
N.Knot
N.Knot

Reputation: 13

SQL trigger to insert row in Table B if a column in Table A has certain value

I am trying to create a trigger for when a user has a certain kind of email to put the user in correct security group. I have an if statement which checks their email but it seems like it's ignoring it.

      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
     ALTER TRIGGER [test].[UpdateSecurityGroup] ON  [test].[user]  

     AFTER INSERT AS begin 

     SET NOCOUNT ON;
     declare @insertedUseremail varchar; 
     select @insertedUseremail = inserted.email from inserted;    
     declare @insertedUserid int = 0;  
     select @insertedUserid = inserted.id from inserted;    

     if (@insertedUseremail like '%@test1.ca%' OR @insertedUseremail like '%@test2.ca%' OR @insertedUseremail like '%@test3.ca%' OR @insertedUseremail like '%@test4.ca%')  
     BEGIN    
     Insert into [test].[usergroup](userid,groupid) Values(@insertedUserid, 3)  
    END
    end

It inserts the user in the user table but does nothing in usergroup table. It should insert in the rows in usergroup like this.

       +----+-----------+---------+
       | ID | userid    | groupid |
       +----+-----------+---------+
       |  1 | 001       |   3     |
       |  2 | 002       |   3     |
       |  3 | 003       |   3     |
       +----+----------+----------+

Upvotes: 1

Views: 51

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269613

You are making a critical mistake by assuming that inserted contains a single row. No, no, no. That is not the way to go.

alter trigger [test].[UpdateSecurityGroup] on  [test].[user] after insert as
begin 
     set nocount on;

    insert into [test].[usergroup](userid, groupid) 
        select i.id, 3
        from inserted i
        where i.email like '%@test1.ca%' OR
              i.email like '%@test2.ca%' OR 
              i.email like '%@test3.ca%' OR 
              i.email like '%@test4.ca%'
end;

If your emails really look like that, you can shorted the where to where i.email like '%@test[1234].ca%.

Upvotes: 2

paulsm4
paulsm4

Reputation: 121629

You need to debug your trigger.

Try this:

How to debug a T-SQL trigger?

  1. Create your trigger, and some test data in one window (to set things up)

  2. Open a new query window, and type in your "insert". Do NOT hit "Run" yet.

  3. Set a breakpoint on that line

  4. Start the debugger (Debug from menu or toolbar or Alt-F5)

  5. Step into your trigger (F11)

Upvotes: 0

Related Questions