none
TRIGGER processing order

    Question

  • Hi!

    I have 3 tables, call them users, domains, and accounts.

    Users has 2 columns: UserID (autogenerated) and Username

    Domains has 2 columns: DomainID and DomainName (pre-populated table)

    Accounts has UserID, DomainID, and Password, where UserID and DomainID are FKs to previous tables and Password is an encrypted field.

    Upon an insert of username, domainname, and cleartext password the following must occur..

    1.) username is inserted into Users, get back an autogenerated userID

    2.) Domains is queried for a match in domainname, get back the DomainID

    3.) call a function to encrypt and store the cleartext password that was given.

    Currently my trigger does step 3 (thanks to Naomi!), but how to define the trigger to perform step 1 and step 2 first, then inserting those values into an account record?

    Tuesday, January 08, 2013 6:27 PM

Answers

  • You can actually create an indexed view and use INSTEAD OF insert statement to insert information using view. In the underlying trigger's code you will do the individual tables inserts. So, it is possible, but this is a bit advanced topic. I think for your purpose it may be simpler to use stored procedure and not use trigger at all.

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


    My blog

    Tuesday, January 08, 2013 9:20 PM
    Moderator

All replies

  • Your question is unclear. You're writing a trigger for Accounts table, right? In this case, what does it do with users and domains?

    If you're talking about a stored procedure, it may be a different matter.


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


    My blog

    Tuesday, January 08, 2013 6:31 PM
    Moderator
  • Sorry, let me clarify...

    Yes, so the trigger is for inserting values into the Accounts table and the users table.

    the Insert will contain the name, username, domainname, cleartext password.

    I have to store the username in the user table, get an ID

    then reference the domain table to get an ID (comparing the domainname I have in the insert statement)

    then call the function to encrypt, in the end storing each found value in accounts

    Tuesday, January 08, 2013 6:54 PM
  • Looks like you're talking about stored procedure, not the trigger. Can you post your stored procedure code? The trigger on Accounts table do not have user name or domain name, it has IDs for these two fields.

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


    My blog

    Tuesday, January 08, 2013 7:06 PM
    Moderator
  • We walked through part of this in a previous post (Trigger intercept?), you had helped by writing this trigger:

    create trigger trMaskYColumn ON myTable INSTEAD OF INSERT

    AS

      
    SET NOCOUNT ON;

      
    INSERT INTO myTable
      
    (PK, Y, Z)

      
    SELECT I.PK, NULL, dbo.Encrypt_Domain_Password(I.Y)

      
    from Inserted I

    So, if we consider myTable the Accounts table as I described above, then I'm expanding it a bit by trying to obtain (and then insert) two values from other tables as I described in the logic for writing to the user table and referencing the domain table.  Those steps need to be completed first, and values obtained before I perform the final INSERT.  I need to write to the user table (and retrieve an autogenerated ID), then reference the domain table (retreive the domain ID), then insert those IDs along with the INSERT statement. 

    Tuesday, January 08, 2013 7:47 PM
  • The INSERTED table used in the trigger only has access to Accounts table columns. So, can you show how your insert statement looks like? 

    You can

    1. Instead of using a trigger use a stored procedure to insert information into related tables

    See this blog post for details

    How to insert information into multiple related tables and return ID using SQLDataSource

    2. Alternatively, if you want to use trigger, you may want to switch back to the AFTER INSERT trigger instead of INSTEAD OF trigger and perform update of the password columns.


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


    My blog


    Tuesday, January 08, 2013 7:52 PM
    Moderator
  •  The insert statement is going to come from Forefront IDM which is writing to the SQL store.

    In fact, I have not created it yet, but it will most likely be a view of the tables.  However I have read that you cannot insert into a view with more than one table, so I'm not exactly sure it will work as I envision.  My goal is to just get the values I need within the trigger or SP routine so I can insert and reference the values in other tables before writing to the main account table and calling the encryption function.  Guess there are multiple ways to do this.  Are you suggesting to use Stored procedures because it is too difficult to perform lookups and write to various tables within a trigger?  I'll look at your example.


    • Edited by Osho27 Tuesday, January 08, 2013 9:09 PM
    Tuesday, January 08, 2013 9:08 PM
  • You can actually create an indexed view and use INSTEAD OF insert statement to insert information using view. In the underlying trigger's code you will do the individual tables inserts. So, it is possible, but this is a bit advanced topic. I think for your purpose it may be simpler to use stored procedure and not use trigger at all.

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


    My blog

    Tuesday, January 08, 2013 9:20 PM
    Moderator
  • Typically though, INSTEAD OF TRIGGERS apply to views, extend view functionality.

    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    Tuesday, January 08, 2013 9:48 PM
    Moderator
  • How would I get the stored procedure to fire off whenever FIM tries to insert?

    How do I pass the values from the insert statement coming across the wire to be considered as input parameters to the SP?

    That is sort of the beauty of triggers here, those two features were available.

    Thanks!

    Wednesday, January 09, 2013 4:20 AM