Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Location
    Akld, NZ
    Posts
    38

    Unanswered: Assistance with Trigger

    Hi,

    Hopefully this will be painless for you guys/gals - however due to my lack of skills/knowledge I need some clarification.

    I have table_X which I have a trigger on INSERT setup.
    This trigger updates Field_2 = '1' and inserts some rows in another table.

    Is there some way that I can restrict this trigger to only run when Field_1 = "BLAH"
    So essentially I am trying to find out how I can pull information/data from the record that fired the trigger and use this in the trigger? (ie to check if Field_1 = "BLAH" and to use Field_3 to further restrict the underlying triggers' updates and inserts)

    Hopefully I have given enough information on this one - if not please let me know any points that I should need to clarify.

    Thanks in advance for your help!!!

    Cheers
    Sideways see's more scenery

  2. #2
    Join Date
    Sep 2003
    Posts
    522
    now, you have to make sure you understand that no matter how many rows you insert, the trigger will fire only once per batch, ok?

    if exists (select * from inserted where field_a = 'blah')
    -- do your stuff

  3. #3
    Join Date
    Nov 2003
    Location
    Akld, NZ
    Posts
    38
    Now that is a prompt response - thanks!

    Yeah I understand that the trigger will only fire once per batch

    So here is another stupid question - where you say select * from inserted. Is inserted a key word here or where you using that as my table name?
    Because basically what I am after is (if it is possible) to have (for example) a record inserted with field_3 = 1234, and field_1 = "BLAH", then the trigger to go "okay" field_1 = BLAH and then carry on and use the field_3 value (ie 1234) in the remaining query to build other records in other tables.

    If what I think is correct - the "inserted" word is actually just my table name - then the trigger will still be firing a lot of times because there is already a lot of records in the table with Field_1 = BLAH.....

    I fear I might be going round in circles and not making a hell of a lot of sense.....*argh*
    Sideways see's more scenery

  4. #4
    Join Date
    Sep 2003
    Posts
    522
    no, inserted is an internal table visible only for the trigger itself. that's where new values resulting from your insert statement are stored.

  5. #5
    Join Date
    Nov 2003
    Location
    Akld, NZ
    Posts
    38
    Sorry to keep asking a million and one questions, but so this table "inserted" which can be queried from the trigger (eg select * from updated), does this have the same structure (ie fields and field names) as the actual table which the data is going to be inserted into?

    And is there any way where I can pull values from that table and use them as variables in the trigger?
    Sideways see's more scenery

  6. #6
    Join Date
    Sep 2003
    Posts
    522
    the answer is YES, except there are only 2 internal table, inserted and deleted. when the number of rows in both is the same it's an update operation, if deleted table is empty then it's an insert. and again, the structure of both is identical to the structure of the table for which the trigger was created.


    edited:

    and yes, you can store all values into variables from inserted, or reference that table throughout the execution of the trigger.

  7. #7
    Join Date
    Nov 2003
    Location
    Akld, NZ
    Posts
    38

    Thumbs up

    Brilliant!!

    Thanks heaps for your help!
    Sideways see's more scenery

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •