How to pass the value to a field in a trigger

[williamkremer]

I think that I'm close here, but I'm getting errors, and what I am attempting to do is to pull that ch.accountno into the @accountno variable so that the update in the contact1 occurs where that accountno (unique) is.
****************************************
CREATE TRIGGER TrgLastCont ON Ch
FOR Update AS
declare @accountno varchar (20)
declare @ondate varchar (11)

if exists (SELECT top 1 cast (ch.ondate as varchar(11)), ch.accountno
from ch where (ch.ondate <> '' or ch.ondate is not null)
and ch.resultcode = 'CT' and ch.ondate = getdate()
order by ch.ondate desc)

Begin
set @accountno = ch.accountno
update Contact1 set Key3 = @ondate
where contact1.accountno = @accountno
end
***********************
The error reads: "Line 13: The column prefix 'ch' does not match with a table name or alias name used in the query." I think I need some learnin' here. Thanks in advance!

Best regards,
Bill

 

[jbenson001]

Get rid of the if exists and change the select:

SELECT top 1 cast (ch.ondate as varchar(11)), [b]@accountno = ch.accountno[/b] 
from ch where (ch.ondate <> '' or ch.ondate is not null) 
and ch.resultcode = 'CT' and ch.ondate = getdate()
order by ch.ondate desc)

Then

IF @accountno is not null then
Begin
 ... your code
End

Jim

 

[williamkremer]

Hmmmmm. The error now is "Incorrect syntax near '=' "
Also, it doesn't like my including the variable assignment inside of the select statement. Maybe I misunderstood:
***********************

SELECT top 1 cast (ch.ondate as varchar(11)), @accountno = ch.accountno
from ch where (ch.ondate <> '' or ch.ondate is not null)
and ch.resultcode = 'CT' and ch.ondate = getdate()
order by ch.ondate desc
IF @accountno is not null then
Begin
update Contact1 set Key3 = @ondate
where contact1.accountno = @accountno
end


Best regards,
Bill

 

[jbenson001]

Sorry, you need to remove the select of the ondate column:

SELECT top 1 @accountno = ch.accountno 
from ch 
where (ch.ondate <> '' or ch.ondate is not null) 
   and ch.resultcode = 'CT' and ch.ondate = getdate()
order by ch.ondate desc

 

[williamkremer]

I cannot remove it from the query because it is the value that I need moved to the other table. (where the accountno = the @accountno)

Best regards,
Bill

 

[jbenson001]

You don't need:

cast (ch.ondate as varchar(11))

You just need to assign the account number based on your criteria. Once you assign the value into the variable, you can use it in the following sql. There is no where in your code that you need ch.ondate other than your WHERE clause, so, there is no reason to SELECT it.

 

[williamkremer]

Yes it is! it's right here:
update Contact1 set Key3 = @ondate

Best regards,
Bill

 

[jbenson001]

ohh sorry I missed that @ondate variable. Then change your SELECT to this:

SELECT top 1 [b]@ondate = cast (ch.ondate as varchar(11)), @accountno = ch.accountno[/b]
from ch where (ch.ondate <> '' or ch.ondate is not null) 
and ch.resultcode = 'CT' and ch.ondate = getdate()
order by ch.ondate desc