Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Aug 2003
    Location
    Oxfordshire
    Posts
    18

    Unanswered: Pass Variables from Trigger to SP ?

    Fairly new to triggers and stored procedures and so far I haven't had to pass any variables or even call an SP from a trigger. The problem I have is to pass a 'callid' variable from an insert trigger to a stored procedure.

    The SP is to delete any rows that already exist that contain the passed 'callid' and then insert the new inserted data (using variables again hopefully).

    Is this at all possible and if so what is the syntax for passing the variable from the trigger and then reading it into the stored procedure ?

    TIA

  2. #2
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    Something like:

    CREATE TRIGGER trg_mytrigger ON tbl_my_table INSEAD OF INSERT
    AS

    DECLARE @callid INT

    SELECT @callid = callid FROM inserted

    --At this point, you can just to the delete from here, rather than calling a seperate sp_, so here are both ways:
    EXEC sp_delete_rows_by_callid @callid

    --or
    DELETE tbl_my_table WHERE callid = @callid

    --then do the insert - (you may want to be more explicit with field names here)

    INSERT tbl_my_table
    SELECT * FROM inserted

    --end of trigger

    sp_code:

    CREATE PROCEDURE sp_delete_rows_by_callid
    @callid INT
    AS
    DELETE tbl_my_table WHERE callid = @callid



    -b
    -bpd

  3. #3
    Join Date
    Aug 2003
    Location
    Oxfordshire
    Posts
    18
    Thanks bpdWork. I have modified the trigger as you suggested but I get an ODBC 3146 database error. Can you see where I may have got the syntax wrong. Thx.

    CREATE TRIGGER trg_mytrigger ON callshistory INSTEAD OF INSERT
    AS

    DECLARE @callid INT

    SELECT @callid = fkey FROM inserted as ins where ins.actiontext = 'hold' and ins.Subactiontext in ('completed','pending user')

    DELETE hold_complete WHERE fkey = @callid


    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    insert hold_complete
    select ins.AddedDT, ins.fkey, ins.actiontext,
    ins.subactiontext, con.emailaddress, ca.loggeddt,
    (con.forename + ' ' + con.surname) as contactname,
    ca.summary, ca.notes,co.coordinator, co.coordinator,getdate(), ca.status,ca.lastsubaction,getdate(),ca.dateopened ,ca.companyname,getdate(),(null),ch.notes
    FROM inserted as ins with (nolock)
    join calls as ca with (nolock)on
    ins.fkey = ca.callid
    join contact as con with (nolock) on
    ca.contactid = con.contactid
    join company as co with (nolock) on
    ca.companyid = co.companyid
    join callshistory as ch with (nolock) on
    ins.historyid = ch.historyid
    where ins.actiontext = 'hold' and ins.Subactiontext in ('completed','pending user')

  4. #4
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    I believe this error means Invalid Object Name, so the problem is probably one of your table names.

    I'm not sure you can alias the "inserted" keyword. Try it without that.
    -bpd

  5. #5
    Join Date
    Aug 2003
    Location
    Oxfordshire
    Posts
    18
    The syntax from SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED was used in my original trigger which allowed the duplicates so I know that bit works. I also tried the SP option you suggested and got no error message but no rows were deleted or inserted.

    Can the inserted table be used outside of the original trigger for the SP ? When does it get deleted ?

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It's only around for the virtual instance the trigger is fired....once the trigger is done, it's gone...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Aug 2003
    Location
    Oxfordshire
    Posts
    18

    Unhappy

    So should this work ? (BTW it don't.)

    CREATE TRIGGER tr_DELETE_DUPES ON Hold_Complete
    after insert AS

    DECLARE @callid varchar

    SELECT @callid = fkey FROM inserted

    delete from hold_complete where addeddt <> (select max(addeddt)from hold_complete) and fkey = @callid

  8. #8
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    delete from hold_complete where addeddt NOT IN (select max(addeddt)from hold_complete) and fkey = @callid
    -bpd

  9. #9
    Join Date
    Aug 2003
    Location
    Oxfordshire
    Posts
    18
    I'm afraid that didn't delete the dupe rows either. Is there anyway that a message can be printed that will show the @callid variable to make sure that's being captured properly

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You need to understand that the virtual tables will hold ALL of the affected rows...not just 1...your code will only grab the last one from the table....

    you need to think set based....


    And if you trying to DELETE dups AFTER the INSERT, I would think you would gewt a dup key error, and the trans would rollback and the trigger won't fire...

    Assuming you have a pk contraint that is....

    Also I would recommend removing the ISOLATION LEVEL code....

    If you check @@ERROR in the sproc that is doing the INSERT you'll catch the error there...

    You can then do an ipdate if you want to change the values...

    I wouldn't do this in a trigger (Unless of course you don't have control over the DML, then I would)....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    CREATE TRIGGER tr_DELETE_DUPES ON Hold_Complete
    after insert AS

    DELETE
    FROM hold_complete
    INNER JOIN inserted
    ON hold_complete.fkey = inserted.fkey
    WHERE addeddt <> (select max(addeddt) from hold_complete)
    -bpd

  12. #12
    Join Date
    Aug 2003
    Location
    Oxfordshire
    Posts
    18
    Brett

    The hold_complete table is updated from an insert trigger on another table when certain criteria are met. This is unlikely to insert more than 1 row at a time. How would you suggest I go about deleting any previous entries with the same callid ?


    bpd

    It didn't like the join on a delete statement.

  13. #13
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    My Bad:

    DELETE hold_complete
    FROM hold_complete
    INNER JOIN inserted
    ON hold_complete.fkey = inserted.fkey
    WHERE addeddt <> (select max(addeddt) from hold_complete)
    -bpd

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by Bracksboy
    Brett

    The hold_complete table is updated from an insert trigger on another table when certain criteria are met. This is unlikely to insert more than 1 row at a time. How would you suggest I go about deleting any previous entries with the same callid ?


    bpd

    It didn't like the join on a delete statement.
    Cascading triggers?

    Also it's not a matter of how likely an event can happen...it's whether it can or it can't...there are no colors here...it's either black or white...

    It's not a matter of IF, it's a matter of WHEN

    First, do you have a Primary Key contraint on CallId now?

    If not, first find out what you're dealing with...

    SELECT CallId, COUNT(*) FROM hold_complete GROUP BY CallId HAVING COUNT(*) > 1

    If that returns nothing, you're in business...and just add the contraint, and you won't have to worry about the trigger at all....

    If it does, then you need to sanatize the data, then add the contraint...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Aug 2003
    Location
    Oxfordshire
    Posts
    18
    Originally posted by Brett Kaiser

    First, do you have a Primary Key contraint on CallId now?

    If not, first find out what you're dealing with...

    SELECT CallId, COUNT(*) FROM hold_complete GROUP BY CallId HAVING COUNT(*) > 1

    If that returns nothing, you're in business...and just add the contraint, and you won't have to worry about the trigger at all....

    If it does, then you need to sanatize the data, then add the contraint...
    I don't have a constraint on callid and I don't think it will work with this. If I understand constraints correctly if I set one on callid it won't allow an insert where that callid already exists. Is that correct ?

    The inserted data is not going to always have a unique callid so I just need to store the latest row of data and remove all previous rows with that callid. The rows that are inserted are tracking details from a call logging system that meet certain criteria and this can have multiple rows with the same callid. From this data I have a VB app that emails confirmation and then sets dates for chasers and automated closure. I need to delete the existing rows so only the latest info is used in the app.

    It's all very new to me.

Posting Permissions

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