none
Need help with creating trigger using instead of insert.

    Question

  • Hi all,

    I am trying to create a trigger that can read the inserted Mail data from table1 and check if the Mail data matches the Mail data from table2. If the Mail data matches the Mail data from table2 it will get the EmpID from table2 and insert it into table1 column EmpID. 

    Here are table2 columns:

    EmpID (int)    Mail(varchar)      Mail2(varchar)
    
    101            test1@test.com     test20@test.com
    
    102            test2@test.com     test21@test.com

    ----------------

    table1 columns 

    EmpID (int)(primary key)   Mail(varchar)    Mail2(varchar)

    ----------------

    If I insert test1@test.com into table1 column Mail, I would like it to get the value for the EmpID from table2 before actually inserting the record into table1, by matching the Mail from table1 = Mail from table2.

    I am using ASP.Net to insert the records into Mail and Mail2.

    How can I achieve that?

    I appreciate any help.


    • Edited by Matt_90 Saturday, June 07, 2014 6:38 PM
    Saturday, June 07, 2014 6:35 PM

Answers

  • It would be something like below

    CREATE TRIGGER Trg_Table1
    ON Table1
    INSTEAD OF INSERT 
    AS
    BEGIN 
    
    INSERT INTO Table1(EmpID,Mail,Mail2)
    SELECT t2.EmpID,i.Mail,i.Mail2
    FROM INSERTED i
    INNER JOIN Table2 t2
    ON t2.Mail = i.Mail
    
    
    END


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by Matt_90 Sunday, June 08, 2014 4:51 AM
    Saturday, June 07, 2014 6:43 PM

All replies

  • It would be something like below

    CREATE TRIGGER Trg_Table1
    ON Table1
    INSTEAD OF INSERT 
    AS
    BEGIN 
    
    INSERT INTO Table1(EmpID,Mail,Mail2)
    SELECT t2.EmpID,i.Mail,i.Mail2
    FROM INSERTED i
    INNER JOIN Table2 t2
    ON t2.Mail = i.Mail
    
    
    END


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by Matt_90 Sunday, June 08, 2014 4:51 AM
    Saturday, June 07, 2014 6:43 PM
  • Note that with Visakh's solution, no rows at all will be inserted if there is no match in table2.

    Overall, an INSTEAD OF trigger sounds like the wrong thing for the problem. Probably you should write a stored procedure for the task.

    A trigger is something which should always happen, no matter from where you perform the operation. Triggers should be defined as part of the database design. It should not be part of the application development.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, June 07, 2014 7:53 PM
  • There should be two SQL statements in the stored procedure in order to accomplish the task?

    Sunday, June 08, 2014 4:51 AM
  • There should be two SQL statements in the stored procedure in order to accomplish the task?

    Ideally you need to include logic as a part of your insert procedure itself. You should have a standard insert stored procedure which should include this logic and should be used for all inserts.

    Also if EmpID field has to have a non NULL value always you may better off creating a foreign key constraint from Table1 to Table2 on EmpID column to enforce the Referential Integrity.


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Sunday, June 08, 2014 5:19 AM