Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2003
    Location
    Oxfordshire
    Posts
    18

    Unanswered: Delete data before trigger executes

    I am trying to delete data from a table prior to populating with new data via a trigger if certain critria matches to eliminate duplicates. I have copied the trigger below. The syntax checks ok but I get any error message saying 'Incorrect syntax near 'GO'. 'ALTER TRIGGER' must be the first statement in a query batch' when I try to save.

    Can someone tell me if this is possible please.

    IF EXISTS (SELECT * FROM hold_complete
    WHERE fkey = hold_complete.fkey AND actiontext = 'hold' and Subactiontext = 'pending user')
    delete from hold_complete where hold_complete.fkey = fkey
    GO
    CREATE TRIGGER tr_hold_complete ON CallsHistory
    for INSERT AS
    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')

  2. #2
    Join Date
    Jan 2004
    Posts
    12
    Does the trigger tr_hold_complete already exist?

  3. #3
    Join Date
    Aug 2003
    Location
    Oxfordshire
    Posts
    18
    Yes it does

  4. #4
    Join Date
    Jan 2004
    Posts
    12
    if it already exist you can't create a trigger with the same name. If you use alter trigger (and the same code), it will modify the trigger without changing anything and your code should work. Not sure if there might be a nicer implementation though

  5. #5
    Join Date
    Aug 2003
    Location
    Oxfordshire
    Posts
    18
    There is only one trigger called tr_hold_complete. I am trying to modify the existing one that currently starts from CREATE TRIGGER (as per above) so that if a row already exists in the table that has the same fkey number as the updated record it deletes the existing record first then inserts the updated row.

    Can that be done in a single trigger ?

  6. #6
    Join Date
    Jan 2004
    Posts
    12
    I think i see the error but a correct implementation eludes me at the moment and I have no access to books online or a sql server to check.

    From the error i would guess that you can't have the syntax before the go statement when either creating or altering the trigger.

    Not sure how to get round this one sorry.

  7. #7
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Originally posted by Bracksboy
    There is only one trigger called tr_hold_complete. I am trying to modify the existing one that currently starts from CREATE TRIGGER (as per above) so that if a row already exists in the table that has the same fkey number as the updated record it deletes the existing record first then inserts the updated row.

    Can that be done in a single trigger ?
    read up on "instead of" triggers in Books online ... I think thats what you are looking for. am not near a sql server right now ... so you will have to look it up.
    Get yourself a copy of the The Holy Book

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

  8. #8
    Join Date
    Aug 2003
    Location
    Oxfordshire
    Posts
    18
    Originally posted by Enigma
    read up on "instead of" triggers in Books online ... I think thats what you are looking for. am not near a sql server right now ... so you will have to look it up.
    Thanks Enigma. Spent most of the PM investigating INSTEAD OF but doesn't seem to do what I require either.

    Can you actually do a Delete from where statement within a trigger 'cos I haven't found one in any of the examples I've searched today.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Forget reading up on instead of triggers...there's just so much wrong here....


    You do not want to do nolock....

    and I don't think (damn that happend a lot) that a trigger will fire for any uncomitted data anyway...

    The entire avenue your heading down shows that your trying to mess with things that you shouldn't

    What are you trying to do...in non technical terms?

    For example you mention you want to prevent dups...

    ok, simple, put a contraint on the columns you would consider to be be dups...

    what else?
    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.

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

    What are you trying to do...in non technical terms?

    For example you mention you want to prevent dups...

    Brett
    The original trigger populates a table that is used to automate emails from our call logging system (with VB). If a call is assigned a certain action (completed or pending user) then the trigger fires. Several mails are sent and if there is no movement on the call after a certain period the call is automatically closed using the VB app.

    The problem I have is that the same call could be released but a few days later given the same action again and unless I can delete the original row the call will be closed on the original closure date.

    Hope this makes it a bit clearer what i am trying to acheive

Posting Permissions

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