PDA

View Full Version : SQL trigger

xauxi
05-16-02, 16:37
I have the following trigger, this line:

if (Select ins.Status from inserted ins) = 'V'

will work only if the select returns one row. If the select return more than one row, it doesn't not work. Would you please help me rewrite this "if" statement so it can work with multiple rows. Thank you very much for your help.

CREATE TRIGGER ApproverReview ON PER_CONTROL
FOR INSERT, UPDATE
AS

if Update(status)
if (select count(*) from inserted, deleted where inserted.Status = deleted.Status) = 0

if (Select ins.Status from inserted ins) = 'V'

BEGIN
UPDATE PER_COMP_ANCHOR_COMMENTS
set evaluator_code = 'B', UPDATE_ID = 'SYSTEM'
where evaluator_code = 'R' and
cntrl_number in (Select ins.per_cntrl_nbr from inserted ins)

END
EdwardP
05-16-02, 16:56
CREATE TRIGGER ApproverReview ON PER_CONTROL
FOR INSERT, UPDATE
AS

if Update(status)
if (select count(*) from inserted, deleted where inserted.Status = deleted.Status) = 0 -- Why do you need this condition ?

-- DELETED if (Select ins.Status from inserted ins) = 'V'

BEGIN
UPDATE PER_COMP_ANCHOR_COMMENTS
set evaluator_code = 'B', UPDATE_ID = 'SYSTEM'
where evaluator_code = 'R' and
cntrl_number in (
Select ins.per_cntrl_nbr from inserted ins
where ins.Status = 'V'
)

END
alligatorsql.com
05-17-02, 04:52
Hello,

I think you have to use a cursor to process your datas:
Instead of using the select statement - use the cursor with open, fetch and close.

Perhaps you can explain a little more detailed, what you would like to do
with the trigger ?

Regards
Manfred
(Alligator Company)
http://www.alligatorsql.com