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

    Wink Unanswered: SQL Trigger updates occasionally

    I have a trigger (code below) that works for some updates equal to the where statement but not all. Very new to triggers so I'm wondering if there are any initial things I need to look for.

    CREATE TRIGGER tr_hold_complete ON CallsHistory
    for INSERT AS
    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
    FROM inserted as ins join calls as ca on
    ins.fkey = ca.callid join contact as con on
    ca.contactid = con.contactid join company as co on
    ca.contactid = co.contactid
    where ins.actiontext = 'hold' and ins.Subactiontext = 'completed'

  2. #2
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: SQL Trigger updates occasionally

    This trigger is defined for fire only on insert. You mention 'update' which I literally take as meaning you need to fire the trigger BOTH on update and insert. Also, make sure the literals you are checking against the columns are the proper case (could those columns have uppercased data?). Something else to consider is the joins. You may have to specify an outer join (are you assured that there are corresponding data in calls, contact, coompany?).


    Originally posted by Bracksboy
    I have a trigger (code below) that works for some updates equal to the where statement but not all. Very new to triggers so I'm wondering if there are any initial things I need to look for.

    CREATE TRIGGER tr_hold_complete ON CallsHistory
    for INSERT AS
    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
    FROM inserted as ins join calls as ca on
    ins.fkey = ca.callid join contact as con on
    ca.contactid = con.contactid join company as co on
    ca.contactid = co.contactid
    where ins.actiontext = 'hold' and ins.Subactiontext = 'completed'

Posting Permissions

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