Trigger Help--and pls Disregard "HI Experts" thread

[williamkremer]

Please disregard the thread "Hi Experts". It has the wrong email address as return address!! This is my first attempt at a trigger, and doing a keyword search on triggers (tek-tips) produced no results, so thus, this post. The SQL Server BOL starts with:
***************
If exists (select name from sysobjects where name = 'NewTrigger' and type = 'TR')
drop trigger NewTrigger
GO

CREATE TRIGGER NewTrigger ON [dbo].[TableName]
FOR INSERT, UPDATE
AS
************
My question is this: Let's say that I want an update to table_x of a certain value in a certain column to trigger an update to table_y. In other words, when the Column_x in table_x gets value 'x' inserted by an application, i'd like column_y in table_y to be updated with value = 'y' on (table_x.accountno = table_y.accountno) I hope this makes sense. And I really appreciate your help, as always. Could you help me to phrase this trigger?

 

[jbenson001]

could you post some sample data as it would be before and after the trigger?

 

[williamkremer]

Sure:
********************
select top 1 cast (ondate as varchar(11)) as OnDate from conthist
where (ondate <> '' or ondate is not null) and resultcode = 'CT'
order by ondate desc
*********************
Result:
Jan 27 06

Now I want to insert 'Jan 27 05' into table_y like this:
*************
update table_y
set HappyDate = OnDate [the result above]
where SecondDateField = Getdate()
and table_x.accountno = table_y.accountno
*******************
That's sort of pseudocode to explain how it should update table_y when someone enters the value 'CT'. When CT is selected from a dropdown list it is written into table_x.resultcode and that should trigger the update to table_y.HappyDate. Yikes!!

 

[jbenson001]

Will you just insert into the second table or is it an update? If it is an update, you need a key column for the where clause.

 

[jbenson001]

Sorry I didn't see the Update. But from your psuedo code, you are updating all the rows in the second table. Do you want to do that, or do you want to update a specific row? If so, a key field must be passed as well.

 

[williamkremer]

Oh, no just one row is to be updated. It will be the row:

UPDATE table_y
SET HappyDate =
(SELECT select top 1 cast (ondate as varchar(11)) as OnDate from Table_x
where (ondate <> '' or ondate is not null) and resultcode = 'CT' order by ondate desc)
JOIN Table_y
ON (Table_x.accountno = table_y.accountno)
where exists (select top 1 table_y.HappyDate from table_y order by HappyDate desc)

Now I've confused myself!!!!