IF-THEN OR CASE TRIGGERS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

IF-THEN OR CASE TRIGGERS

Author

Topic

Scullpa
Starting Member

3 Posts

Posted - 2003-07-10 : 17:06:23

I'm trying to write a trigger that will update a field based on what type of entry is being inserted. Type being either a 'PY' or ELSE. The value that will be populated in that field will be based on a substring from one of two other tables. Thus far, I have not been able to get it to work. I have tried it both as an IF-THEN (didn't know if they work in triggers) and a CASE. Any help would be greatly appreciated!

Thanks!

--------------------------------
CREATE TRIGGER FixAPATRPayments
ON APATR
FOR INSERT, UPDATE
as
update APATR

IF I.TYPEBTCH = 'PY'
SET I.IDACCTSET = SUBSTRING(B.IDACCT,CHARINDEX('-',RTRIM(B.IDACCT))+1,2)
FROM Inserted I inner join BKACCT B
ON BKACCT.BANK = I.IDBANK
ELSE
set I.IDACCTSET = SUBSTRING(I.IDAPACCT,CHARINDEX('-',RTRIM(I.IDAPACCT))+1,2)
from Inserted I inner join APATR
on APATR.IDVEND = I.IDVEND
AND APATR.IDINVC = I.IDINVC
go

nr
SQLTeam MVY

12543 Posts

Posted - 2003-07-11 : 01:12:55

try

update APATR
SET I.IDACCTSET = SUBSTRING(B.IDACCT,CHARINDEX('-',RTRIM(B.IDACCT))+1,2)
FROM Inserted I inner join BKACCT B
ON BKACCT.BANK = I.IDBANK
where I.TYPEBTCH = 'PY'

update APATR
set I.IDACCTSET = SUBSTRING(I.IDAPACCT,CHARINDEX('-',RTRIM(I.IDAPACCT))+1,2)
from Inserted I inner join APATR
on APATR.IDVEND = I.IDVEND
AND APATR.IDINVC = I.IDINVC
where I.TYPEBTCH <> 'PY'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources