locked
After Insert Trigger RRS feed

  • Question

  • Hi

    My Tsql has never been my strong point, here are my tables and what i am trying to do. :)

    UsersTable

    UserTemplatesTable

    UsersettingsTable

    What i want to do is create a trigger that after a new records has been inserted into UsersTable it has to insert all the records from UserTemplatesTable along with the Identity field from UserTable into  UsersettingsTable

    In short im just copying all the records from UserTemplateTable to UserSettingTable along witht he Identity field of the record insert from UsersTable to that i have a relation between the 2 tables.

    Thanks

    Tuesday, September 20, 2011 2:18 PM

Answers

  • Are you sure you want to use trigger for that and not the stored procedure?

    In any case, the trigger will be something like this:

     

    create trigger trUsers_Insert ON UsersTable AFTER INSERT 
    
    AS
    
    if exists (select 1 from Inserted)
    
      insert into UserSettings
    
      (UserID, other fields)
    
       select I.UserID, T.*
    
        from Inserted I cross join UserTemplates T 
    

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by ETS Tuesday, September 20, 2011 6:40 PM
    Tuesday, September 20, 2011 2:29 PM

All replies

  • Assuming UserID is the identity column in your tables, try this:

     

    CREATE TRIGGER copyRecords ON dbo.Users AFTER INSERT
    AS
    INSERT UserSettings (Col1,..., Coln, UserID)
    SELECT a.Col1,.., a.Coln, i.UserID 
    FROM UserTemplates a INNER JOIN Inserted i ON (a.UserID = b.UserID);
    
    

    In SQL Server, we do not add additional qualifiers -- such as 'tbl', or 'table' or 't' -- to our table names to indicate the object is a table, hence the reason I skipped them.

    Also, if this is not what you are looking for, I suggest you post your complete table definitions, sample data, and expected results.


    MG.-
    Mariano Gomez, MIS, MCITP, PMP
    IntellPartners, LLC.
    http://www.intellpartners.com/
    Blog http://dynamicsgpblogster.blogspot.com/
    Tuesday, September 20, 2011 2:28 PM
  • Are you sure you want to use trigger for that and not the stored procedure?

    In any case, the trigger will be something like this:

     

    create trigger trUsers_Insert ON UsersTable AFTER INSERT 
    
    AS
    
    if exists (select 1 from Inserted)
    
      insert into UserSettings
    
      (UserID, other fields)
    
       select I.UserID, T.*
    
        from Inserted I cross join UserTemplates T 
    

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by ETS Tuesday, September 20, 2011 6:40 PM
    Tuesday, September 20, 2011 2:29 PM
  • Hi

    Thanks for the help witht he trigger.

    Why would you suggest using a stored proc and not a trigger in this instance.

    My idea would be that its just less over head on the app and client side if i use a trigger.

    thanks again

    Wednesday, September 21, 2011 5:49 AM