If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Data Access, Manipulation & Batch Languages > ANSI SQL > SQL Trigger updates occasionally

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 08-22-03, 06:11
Bracksboy Bracksboy is offline
Registered User
 
Join Date: Aug 2003
Location: Oxfordshire
Posts: 18
Wink 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'
Reply With Quote
  #2 (permalink)  
Old 08-25-03, 08:35
dmmac dmmac is offline
Registered User
 
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?).


Quote:
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'
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On