Trigger (deleting more than one row)

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

SQL Server Development (2000)

Trigger (deleting more than one row)

Author

Topic

CLages
Posting Yak Master

116 Posts

Posted - 2004-03-09 : 15:32:37

i have two Tables PEDIDO and PEDIDO1
for each row in PEDIDO i can have several in PEDIDO1
PEDIDO PEDIDO1
123 123 01
123 02
etc
124 124 01

There is a cascading delete , If I delete 123 all rows in PEDIDO1
should be deleted too.

and i have one trigger in Table PEDIDO1
everything is fine, but only if i have one row in PEDIDO1
if i have more than one ROW i got error like this.

(Deleted fails because was returned more than one ROW , etc etc) .

if I drop the Trigger, works fine.
of course my problems is in trigger.

any help ?

tks

Carlos Lages

this trigger in a Table PEDIDO1

CREATE TRIGGER [SubtraiSaldoPed1] ON [dbo].[Pedido1]
FOR DELETE
AS
IF (SELECT ped1_atualizaestoque FROM DELETED ) = 'S'
if (SELECT ped1_estoque FROM DELETED) = 1
update produto
set
pro_reserv1 = pro_reserv1 - d.ped1_quantp2 from deleted d
where
pro_deposito = d.ped1_deposito and
pro_codigo = d.ped1_produto
else
update produto
set
pro_reserv2 = pro_reserv2 - d.ped1_quantp2 from deleted d
where
pro_deposito = d.ped1_deposito and
pro_codigo = d.ped1_produto;

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-09 : 15:49:15

The problem is in the IF statements. You need to move them into the UPDATE statements. Something like this:

update produto
set
pro_reserv1 = pro_reserv1 - d.ped1_quantp2 from deleted d
where
pro_deposito = d.ped1_deposito and
pro_codigo = d.ped1_produto and d.ped1_atualizaestoque = 'S' AND p.ped1_estoque = 1

update produto
set
pro_reserv2 = pro_reserv2 - d.ped1_quantp2 from deleted d
where
pro_deposito = d.ped1_deposito and
pro_codigo = d.ped1_produto AND d.ped1_atualizaestoque <> 'S' AND d.ped1_estoque <> 1

I'm sure that the above won't work, but the point is that you can't check multiple rows in the IF. So you need to handle the conditions in the UPDATE or some other way.

Tara

CLages
Posting Yak Master

116 Posts

Posted - 2004-03-09 : 18:56:44

I solve it by my self using Cursor because delete is deleting more than one row, then i made a Cursor , from each fetch , I do an Update.
it is working fine.

is there a better way to do this?

the only thing i dont undestood is why can not i use the collumn names
from deleted table like d.mov_qt , etc
i had to fetch into @qt and in Updade use @qt instead d.mov_qt.

tks anyway
Carlos Lages

/****** Object: Trigger dbo.SubtraiSaldomov Script Date: 12/01/2004 13:46:28 ******/
CREATE TRIGGER [SubtraiSaldomov] ON [dbo].[Moviment]
FOR DELETE
AS

SET NOCOUNT ON
DECLARE @QT DECIMAL(13,6) ,
@MERCADORIA DECIMAL(11,2) ,
@VLVALE DECIMAL(11,2),
@deposito char(3),
@codigo char(14),
@estoque int,
@flag int

DECLARE authors_cursor CURSOR FOR
SELECT d.MOV_QT ,
d.MOV_MERCADORIA,
d.MOV_VLVALE ,
d.mov_deposito,
d.mov_codigo,
d.mov_estoque,
d.mov_flag
FROM DELETED D

OPEN authors_cursor

FETCH NEXT FROM authors_cursor
INTO @QT,
@MERCADORIA ,
@VLVALE ,
@deposito,
@codigo,
@estoque,
@flag

WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN
IF @estoque = 1
if @flag = 1
update produto
set
pro_saldo_fis1 = pro_saldo_fis1 - @qt,
pro_saldo_fin1 = pro_saldo_fin1 - @mercadoria - @vlvale
where
pro_deposito = @deposito and
pro_codigo = @codigo
else
update produto
set
pro_saldo_fis1 = pro_saldo_fis1 + @qt,
pro_saldo_fin1 = pro_saldo_fin1 + @mercadoria - @vlvale
where
pro_deposito = @deposito and
pro_codigo = @codigo
else
if @flag = 1
update produto
set
pro_saldo_fis2 = pro_saldo_fis1 - @qt,
pro_saldo_fin2 = pro_saldo_fin1 - @mercadoria - @vlvale
where
pro_deposito = @deposito and
pro_codigo = @codigo
else
update produto
set
pro_saldo_fis2 = pro_saldo_fis1 + @qt,
pro_saldo_fin2 = pro_saldo_fin1 + @mercadoria - @vlvale
where
pro_deposito = @deposito and
pro_codigo = @codigo;
END

FETCH NEXT FROM authors_cursor
INTO @QT, @MERCADORIA , @VLVALE , @deposito, @codigo, @estoque, @flag
END

CLOSE authors_cursor
DEALLOCATE authors_cursor

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-09 : 19:06:43

You should not be using a cursor for this. SQL can handle multiple rows. And you can reference columns from the trigger table.

Tara

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-03-09 : 23:45:14

And here's how:

CREATE TRIGGER [SubtraiSaldomov] ON [dbo].[Moviment] FOR DELETE AS
SET NOCOUNT ON

UPDATE P
SET pro_saldo_fis1 = CASE WHEN D.mov_estoque <> 1 THEN P.pro_saldo_fis1
WHEN D.mov_flag = 1 THEN P.pro_saldo_fis1-d.mov_qt
ELSE P.pro_saldo_fis1+d.mov_qt END,

pro_saldo_fin1 = CASE WHEN D.mov_estoque <> 1 THEN P.pro_saldo_fin1
WHEN D.mov_flag = 1 THEN P.pro_saldo_fin1-d.mov_mercadoria-d.mov_vlvale
ELSE P.pro_saldo_fin1+d.mov_mercadoria-d.mov_vlvale END,

pro_saldo_fis2 = CASE WHEN D.mov_estoque = 1 THEN P.pro_saldo_fis2
WHEN D.mov_flag = 1 THEN P.pro_saldo_fis1-d.mov_qt
ELSE P.pro_saldo_fis1+d.mov_qt END,

pro_saldo_fin2 = CASE WHEN D.mov_estoque = 1 THEN P.pro_saldo_fin2
WHEN D.mov_flag = 1 THEN P.pro_saldo_fin1-d.mov_mercadoria-d.mov_vlvale
ELSE P.pro_saldo_fin1+d.mov_mercadoria-d.mov_vlvale END

FROM Produto P INNER JOIN deleted D ON P.mov_deposito=d.mov_deposito AND
P.mov_codigo=d.mov_codigo

CLages
Posting Yak Master

116 Posts

Posted - 2004-03-10 : 07:51:22

tks, i fix some bugs in your Scripts, and I got the Main idea.
the new trigger now works fine .
now i have two option using Cursor and using INNER JOIN.
the new script is now.
tks again

Carlos Lages
CREATE TRIGGER [SubtraiSaldomovx] ON [dbo].[Moviment] FOR DELETE AS
SET NOCOUNT ON

UPDATE P
SET
pro_saldo_fis1 = CASE
WHEN D.mov_estoque = 1 AND D.mov_flag = 1
THEN P.pro_saldo_fis1 - d.mov_qt
WHEN D.mov_estoque = 1 AND D.mov_flag = 2
THEN P.pro_saldo_fis1 + d.mov_qt
END,

pro_saldo_fin1 = CASE
WHEN D.mov_estoque = 1 AND D.mov_flag = 1
THEN P.pro_saldo_fin1 - d.mov_mercadoria - d.mov_vlvale
WHEN D.mov_estoque = 1 AND D.mov_flag = 2
THEN P.pro_saldo_fin1 + d.mov_mercadoria + d.mov_vlvale
END,

pro_saldo_fis2 = CASE
WHEN D.mov_estoque = 2 AND D.mov_flag = 1
THEN P.pro_saldo_fis2 - d.mov_qt
WHEN D.mov_estoque = 2 AND D.mov_flag = 2
THEN P.pro_saldo_fis2 + d.mov_qt
END,

pro_saldo_fin2 = CASE
WHEN D.mov_estoque = 2 AND D.mov_flag = 1
THEN P.pro_saldo_fin2 - d.mov_mercadoria - d.mov_vlvale
WHEN D.mov_estoque = 2 AND D.mov_flag = 2
THEN P.pro_saldo_fin2 + d.mov_mercadoria + d.mov_vlvale
END

FROM Produto P INNER JOIN deleted D ON
P.pro_deposito = d.mov_deposito AND
P.pro_codigo = d.mov_codigo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources