My Trigger isn't triggering

[williamkremer]

When the sales rep updates a record with the value 'CT' we want to insert a date in another table (cast as Varchar, cuz it's a varchar field) This was the trigger I wrote but it ain't triggering!! (Key3 is not updating with the Date)
***********************************
USE SalesCrm_06
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)

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 = getdate()
order by conthist.ondate desc

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

Best regards,
Bill

 

[SQLDenis]

because of this
and conthist.resultcode = 'CT' and conthist.ondate = getdate()

getdate() will always be greater than the value in the inserted table

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[williamkremer]

Ah, yes. So I could say -- where month(ondate) = month(getdate()) and day(ondate) = day(getdate()) and year(ondate) = year(getdate()), but is there an easier way to do this. It seems very wordy.
Thanks Denis

Best regards,
Bill

 

[SQLDenis]

sure

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

and then

and conthist.ondate >= @DateNow
and conthist.ondate < dateadd(d,1, @DateNow)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[williamkremer]

You are a wizard.

Best regards,
Bill