"Trigger isn't Triggering" -- the Sequel

[williamkremer]

Get it? The SQL? Har har. Anyway, this isn't working and I can't see what's wrong. When I update Conthist with the value 'CT' that's supposed to update contact1 (the Begin--End block) Thanks once again.
******************************
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

Best regards,
Bill

 

[SQLDenis]

conthist should be inserted

so

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 (i.ondate as varchar(11)),
@accountno = i.accountno, @resultcode = i.resultcode 
from inserted i where (i.ondate <> '' or i.ondate is not null) 
and i.resultcode = 'CT' and i.ondate >= @DateNow
and i.ondate < dateadd(d,1, @DateNow)
order by i.ondate desc

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

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[williamkremer]

Now I'm really lost. I'm trying to update contact1. I don't understand. And, Key3 isn't updating with the @ondate.

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

 

[SQLDenis]

put some print statments in the trigger and run update from query analyzer

right after the select
print '@ondate = ' + @ondate
print '@accountno= ' + @accountno
print '@resultcode = ' + @resultcode

then look at the values from the messages tab

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[williamkremer]

None. No values.

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

 

[SQLDenis]

Ok now you know the problem is here

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

do you need this?
where (i.ondate <> '' or i.ondate is not null)
also is it possible that i.resultcode = 'CT' is maybe 'CT '
maybe you need RTRIM(i.resultcode )

you start taking out coditions until you see data and then figure out the problem (nulls, leading/trailing spaces ETC ETC)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[williamkremer]

Ok, thanks for all the advice. I'll figure this thing out eventually.

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

 

[williamkremer]

Maybe it't here Denis:
if @resultcode = 'CT'
Maybe I should have something like If Update something-or other?

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

 

[SQLDenis]

you need to check for the value no matter what

update table set resultcode ='CT' where resultcode = 'CT'

will make if update(resultcode) true even if the value hasn't changed

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/