Remember this trigger?

[williamkremer]

Hi all, I never did get this trigger to update the Contact1 table when 'CT' value is entered into Conthist. Simply put, nothing at all happens! I gave up on it for a while, but now I'd like to revisit the thing. Thanks for any insights ..
**************************
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'TrgLastCont' AND type = 'TR')
DROP TRIGGER TrgLastCont
GO
CREATE TRIGGER TrgLastCont ON Conthist
FOR Update AS
declare @accountno varchar (20)
declare @ondate varchar (11)
declare @resultcode varchar(3)

declare @DateNow datetime
select @DateNow = convert(varchar,getdate(),110)

SELECT top 1 @ondate = cast (conthist.ondate as varchar(11)),
@accountno = conthist.accountno, @resultcode = conthist.resultcode
from conthist where (conthist.ondate <> '' or conthist.ondate is not null)
and conthist.resultcode = 'CT' and conthist.ondate >= @DateNow
and conthist.ondate < dateadd(d,1, @DateNow)
order by conthist.ondate desc

if @resultcode = 'CT'
Begin
update Contact1 set Key3 = @ondate
where contact1.accountno = @accountno
end


"You cannot hold a torch to another man's path without brightening your own"
Best regards,
Bill

 

[SQLSister]

In the first place you want to use the inserted pseudotable to get the records which have the proper code. THen you want to do a set-based insert rather than use the top 1 or you will miss items when multiple records are inserted.

update Contact1 
set Key3 = cast (i.ondate as varchar(11))
from Contact1 c join inserted i on 
on c.accountno = i.accountno 
where (i.ondate <> '' or i.ondate is not null) 
and i.resultcode = 'CT' and i.ondate >= convert(varchar,getdate(),110)
and conthist.ondate < dateadd(d,1, convert(varchar,getdate(),110))

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[williamkremer]

What?

"You cannot hold a torch to another man's path without brightening your own"
Best regards,
Bill

 

[SQLSister]

Triggers hae two psuedotables (inserted and deleted) that hold the information being changed. Inserted has the new values. You use these to get the information you need to send to another tabble inthe trigger. I wrote it so it would process all possible rows inthe table in one commeans rather than only looking at the last one whcihc is what you were showing. Updates can happen to multiple recrods simulatnerously and then the isnerted table will have multiple records. All triggers must account for this or you will not get consistent results.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[williamkremer]

Thanks for that explanation. Now I'll have to think it over. My brain is acting like a brain on Mondays. Oh, wait! It IS Monday.

"You cannot hold a torch to another man's path without brightening your own"
Best regards,
Bill

 

[williamkremer]

Ok, I'm back. So, are you saying that my Select statement goes away? Also, how does sql server know where the data is coming from? (it is supposed to come out of conthist and be updated into contact1) The longer this goes on the more confused I get. I thought it would be easy: when conthist has a record inserted which contains a 'CT' I want the date that this happens (generated automatically in conthist as ondate)placed into contact1 table.

"You cannot hold a torch to another man's path without brightening your own"
Best regards,
Bill

 

[SQLSister]

Again, in a trigger (and only in a trigger) there are two imaginary tables inserted and deleted. These tables contain the records that are being inserted or deleted or updated (in this case deleted contains the old values and inserted contains the new ones). In a trigger you generally want to pull the data from one or both of these tables that you need. Pulling from the table itself is usually less efficient as you have to search all the rows not just the ones that were inserted, deleted or updated.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[Ultradiv]

William,
looking at your existing code, are you simply using the event of an update on the table to trigger the highlighting of the next ondate?

or

Is the data that is updated the same data that is being manipulated by your trigger?

If the answer is the first then you wont be needing the Deleted or Inserted tables (because the data youre manipulating is unrelated to the update)

 

[williamkremer]

Here is what I want to happen: the instant that a record is created in Conthist table with a resultcode = 'CT' a timestamp is also created in that record. I want that timestamp to then be inserted into table Contact2 where conthist.accountno = contact2.accountno in the U_Time column. Hope this makes sense!!

"You cannot hold a torch to another man's path without brightening your own"
Best regards,
Bill