Trigger y actualizaciones de más de una fila.

16/02/2007 - 14:07 por Salvador Ramos | Informe spam

Hola a todos,

Estoy preparando un ejemplo didáctico para demostrar los problemas que
podemos tener si no utilizamos correctamente el código en nuestros triggers,
básicamente es para demostrar que un trigger nos puede funcionar bien para
actualizaciones (insert, update o delete) que afecten a una sola fila, pero
cuando afecten a más de una fila nos puede fallar. Esa parte ya la tengo
hecha, ahora lo que quiero es modificar el trigger para que las
actualizaciones que afecten a más de una fila también funcionen
correctamente. He contemplado varias maneras de hacerlo, una es con una
tabla temporal. Lo que me gustaría, ya que es con un fin didactico, que me
propusiesis el código del trigger que consideréis más optimo para resolver
la situación.

Este es el código que tengo para el ejemlo, incluyendo las tablas y diversas
actualizaciones:
USE Pruebas
DROP TABLE [dbo].[Articulos]
CREATE TABLE [dbo].[Articulos] (
[Codigo] [int] NOT NULL ,
[Nombre] [varchar] (30) COLLATE Modern_Spanish_CI_AS NOT NULL ,
[Existencias] [int] NOT NULL
) ON [PRIMARY]
DROP TABLE [dbo].[Ventas]
CREATE TABLE [dbo].[Ventas] (
[NumVenta] [int] IDENTITY (1, 1) NOT NULL ,
[Articulo] [int] NOT NULL ,
[Cantidad] [int] NOT NULL ,
[Precio] [int] NOT NULL
) ON [PRIMARY]
CREATE TRIGGER trg_ActExistencias ON Ventas
FOR INSERT, UPDATE, DELETE
as
UPDATE Articulos
SET Existencias = Existencias + Deleted.Cantidad
FROM Deleted inner join Articulos on Deleted.Articulo = Articulos.Codigo
UPDATE Articulos
SET Existencias = Existencias - Inserted.Cantidad
FROM Inserted inner join Articulos on Inserted.Articulo = Articulos.Codigo

DELETE Articulos
DELETE Ventas
INSERT INTO Articulos(Codigo, Nombre, Existencias)
VALUES(1,'Tornillo 7mm',100)
INSERT INTO Articulos(Codigo, Nombre, Existencias)
VALUES(2,'Tornillo 10mm',200)
INSERT INTO Articulos(Codigo, Nombre, Existencias)
VALUES(3,'Tornillo 15mm',300)
SELECT * FROM Articulos
SELECT * FROM Ventas
INSERT INTO Ventas(Articulo, Cantidad, Precio)
VALUES(1,10,1000)
INSERT INTO Ventas(Articulo, Cantidad, Precio)
VALUES(2,12,1000)
INSERT INTO Ventas(Articulo, Cantidad, Precio)
VALUES(3,13,1000)
INSERT INTO Ventas(Articulo, Cantidad, Precio)
VALUES(1,20,1000)
SELECT * FROM Articulos
SELECT * FROM Ventas
UPDATE Ventas
SET Cantidad = Cantidad + 1
SELECT * FROM Articulos
UPDATE Ventas
SET Cantidad = Cantidad - 1
SELECT * FROM Articulos
DELETE Ventas
WHERE Articulo = 1
DELETE Ventas
WHERE Articulo = 2
DELETE Ventas
WHERE Articulo = 3
SELECT * FROM Articulos
SELECT * FROM Ventas

Espero vuestras sugerencias, muchas gracias.

Un saludo
Salvador Ramos
Murcia - España

[Microsoft MVP SQL Server / MCTS: SQL Server 2005]
www.helpdna.net (información sobre SQL Server y .NET)
Y ahora también en www.sqlserverymicrosoft.net

Siga el debate

8 respuestas

Tengo una respuesta

Preguntas similare

Mas problemas con svchost y las actualizaciones automaticas

Leer las respuestas

#1 Alejandro Mesa

16/02/2007 - 15:01 | Informe spam

Hola Salvador,

Estas sentencias toman en cuenta multiples filas afectadas por una sentencia
DML. Puedes cambiar la sintaxis y usar JOIN al estilo Microsoft.

UPDATE dbo.Articulos
SET Existencias = Existencias + (
select sum(d.Cantidad)
from deleted as d
where d.Articulo = dbo.Articulos.Codigo
)
where
exists (
select *
from deleted as d
where d.Articulo = dbo.Articulos.Codigo
)

UPDATE dbo.Articulos
SET Existencias = Existencias - (
select sum(i.Cantidad)
from inserted as i
where i.Articulo = dbo.Articulos.Codigo
)
where
exists (
select *
from inserted as i
where i.Articulo = dbo.Articulos.Codigo
)

AMB

"Salvador Ramos" wrote:

Hola a todos,

Estoy preparando un ejemplo didáctico para demostrar los problemas que
podemos tener si no utilizamos correctamente el código en nuestros triggers,
básicamente es para demostrar que un trigger nos puede funcionar bien para
actualizaciones (insert, update o delete) que afecten a una sola fila, pero
cuando afecten a más de una fila nos puede fallar. Esa parte ya la tengo
hecha, ahora lo que quiero es modificar el trigger para que las
actualizaciones que afecten a más de una fila también funcionen
correctamente. He contemplado varias maneras de hacerlo, una es con una
tabla temporal. Lo que me gustaría, ya que es con un fin didactico, que me
propusiesis el código del trigger que consideréis más optimo para resolver
la situación.

Este es el código que tengo para el ejemlo, incluyendo las tablas y diversas
actualizaciones:
USE Pruebas
DROP TABLE [dbo].[Articulos]
CREATE TABLE [dbo].[Articulos] (
[Codigo] [int] NOT NULL ,
[Nombre] [varchar] (30) COLLATE Modern_Spanish_CI_AS NOT NULL ,
[Existencias] [int] NOT NULL
) ON [PRIMARY]
DROP TABLE [dbo].[Ventas]
CREATE TABLE [dbo].[Ventas] (
[NumVenta] [int] IDENTITY (1, 1) NOT NULL ,
[Articulo] [int] NOT NULL ,
[Cantidad] [int] NOT NULL ,
[Precio] [int] NOT NULL
) ON [PRIMARY]
CREATE TRIGGER trg_ActExistencias ON Ventas
FOR INSERT, UPDATE, DELETE
as
UPDATE Articulos
SET Existencias = Existencias + Deleted.Cantidad
FROM Deleted inner join Articulos on Deleted.Articulo = Articulos.Codigo
UPDATE Articulos
SET Existencias = Existencias - Inserted.Cantidad
FROM Inserted inner join Articulos on Inserted.Articulo = Articulos.Codigo

DELETE Articulos
DELETE Ventas
INSERT INTO Articulos(Codigo, Nombre, Existencias)
VALUES(1,'Tornillo 7mm',100)
INSERT INTO Articulos(Codigo, Nombre, Existencias)
VALUES(2,'Tornillo 10mm',200)
INSERT INTO Articulos(Codigo, Nombre, Existencias)
VALUES(3,'Tornillo 15mm',300)
SELECT * FROM Articulos
SELECT * FROM Ventas
INSERT INTO Ventas(Articulo, Cantidad, Precio)
VALUES(1,10,1000)
INSERT INTO Ventas(Articulo, Cantidad, Precio)
VALUES(2,12,1000)
INSERT INTO Ventas(Articulo, Cantidad, Precio)
VALUES(3,13,1000)
INSERT INTO Ventas(Articulo, Cantidad, Precio)
VALUES(1,20,1000)
SELECT * FROM Articulos
SELECT * FROM Ventas
UPDATE Ventas
SET Cantidad = Cantidad + 1
SELECT * FROM Articulos
UPDATE Ventas
SET Cantidad = Cantidad - 1
SELECT * FROM Articulos
DELETE Ventas
WHERE Articulo = 1
DELETE Ventas
WHERE Articulo = 2
DELETE Ventas
WHERE Articulo = 3
SELECT * FROM Articulos
SELECT * FROM Ventas

Espero vuestras sugerencias, muchas gracias.

Un saludo
Salvador Ramos
Murcia - España

[Microsoft MVP SQL Server / MCTS: SQL Server 2005]
www.helpdna.net (información sobre SQL Server y .NET)
Y ahora también en www.sqlserverymicrosoft.net

Responder a este mensaje

#2 Juan Diego Bueno

16/02/2007 - 15:04 | Informe spam

Hola Salvador:

No he mirado muy profundamente el código, pero yo cuando programo un
trigger (y cada vez tiendo menos a ello, por la guerra que me dan),
tengo en cuenta siempre que haya más de una fila afectada. No se si es
lo que buscas o no, pero yo tengo por costumbre utilizar un cursor que
recorre inserted o deleted según el tipo de trigger y actuar a partir
de ahí. Tampoco se si para el rendimiento eso es bueno o no, pero
normalmente es mi forma de trabajar. Lo veo como una propuesta de
solución

Saludos

On 16 feb, 14:07, "Salvador Ramos"
wrote:

Hola a todos,

Estoy preparando un ejemplo didáctico para demostrar los problemas que
podemos tener si no utilizamos correctamente el código en nuestros triggers,
básicamente es para demostrar que un trigger nos puede funcionar bien para
actualizaciones (insert, update o delete) que afecten a una sola fila, pero
cuando afecten a más de una fila nos puede fallar. Esa parte ya la tengo
hecha, ahora lo que quiero es modificar el trigger para que las
actualizaciones que afecten a más de una fila también funcionen
correctamente. He contemplado varias maneras de hacerlo, una es con una
tabla temporal. Lo que me gustaría, ya que es con un fin didactico, que me
propusiesis el código del trigger que consideréis más optimo para resolver
la situación.

Este es el código que tengo para el ejemlo, incluyendo las tablas y diversas
actualizaciones:
USE Pruebas
DROP TABLE [dbo].[Articulos]
CREATE TABLE [dbo].[Articulos] (
[Codigo] [int] NOT NULL ,
[Nombre] [varchar] (30) COLLATE Modern_Spanish_CI_AS NOT NULL ,
[Existencias] [int] NOT NULL
) ON [PRIMARY]
DROP TABLE [dbo].[Ventas]
CREATE TABLE [dbo].[Ventas] (
[NumVenta] [int] IDENTITY (1, 1) NOT NULL ,
[Articulo] [int] NOT NULL ,
[Cantidad] [int] NOT NULL ,
[Precio] [int] NOT NULL
) ON [PRIMARY]
CREATE TRIGGER trg_ActExistencias ON Ventas
FOR INSERT, UPDATE, DELETE
as
UPDATE Articulos
SET Existencias = Existencias + Deleted.Cantidad
FROM Deleted inner join Articulos on Deleted.Articulo = Articulos.Codigo
UPDATE Articulos
SET Existencias = Existencias - Inserted.Cantidad
FROM Inserted inner join Articulos on Inserted.Articulo = Articulos.Codigo

DELETE Articulos
DELETE Ventas
INSERT INTO Articulos(Codigo, Nombre, Existencias)
VALUES(1,'Tornillo 7mm',100)
INSERT INTO Articulos(Codigo, Nombre, Existencias)
VALUES(2,'Tornillo 10mm',200)
INSERT INTO Articulos(Codigo, Nombre, Existencias)
VALUES(3,'Tornillo 15mm',300)
SELECT * FROM Articulos
SELECT * FROM Ventas
INSERT INTO Ventas(Articulo, Cantidad, Precio)
VALUES(1,10,1000)
INSERT INTO Ventas(Articulo, Cantidad, Precio)
VALUES(2,12,1000)
INSERT INTO Ventas(Articulo, Cantidad, Precio)
VALUES(3,13,1000)
INSERT INTO Ventas(Articulo, Cantidad, Precio)
VALUES(1,20,1000)
SELECT * FROM Articulos
SELECT * FROM Ventas
UPDATE Ventas
SET Cantidad = Cantidad + 1
SELECT * FROM Articulos
UPDATE Ventas
SET Cantidad = Cantidad - 1
SELECT * FROM Articulos
DELETE Ventas
WHERE Articulo = 1
DELETE Ventas
WHERE Articulo = 2
DELETE Ventas
WHERE Articulo = 3
SELECT * FROM Articulos
SELECT * FROM Ventas

Espero vuestras sugerencias, muchas gracias.

Un saludo
Salvador Ramos
Murcia - España

[Microsoft MVP SQL Server / MCTS: SQL Server 2005]www.helpdna.net(información sobre SQL Server y .NET)
Y ahora también enwww.sqlserverymicrosoft.net

Responder a este mensaje

#3 Penta

16/02/2007 - 19:24 | Informe spam

Hola.
No he probado el código, pero si en el trigger usas el Group by para
realizar la actualizacion ??

Salu2.
Penta.

Responder a este mensaje

#4 Salvador Ramos

19/02/2007 - 10:16 | Informe spam

Hola,

Muchas gracias por vuestra colaboración.
El trigger que he mostrado es un ejemplo para comprobar que así no se
trabaja con triggers, ya que realmente no se está obteniendo el resultado
esperado, al no afectar la operación a todas las filas implicadas. Lo que
busco es encontrar una solución optima, para mostrarla a nivel didáctico,
por ello descarto el uso de cursores, tablas temporales y demás.

Voy a optar por la solución propuesta por Alejandro.

Un saludo
Salvador Ramos
Murcia - España

[Microsoft MVP SQL Server / MCTS: SQL Server 2005]
www.helpdna.net (información sobre SQL Server y .NET)
Y ahora también en www.sqlserverymicrosoft.net

"Penta" escribió en el mensaje
news:
Hola.
No he probado el código, pero si en el trigger usas el Group by para
realizar la actualizacion ??

Salu2.
Penta.

Responder a este mensaje

#5 Salvador Ramos

19/02/2007 - 10:18 | Informe spam

Hola Alejandro,

Me parece una buena solución, que además evita el uso de cursores o tablas
temporales para acumular. Ahora lo que no entiendo es qué te refieres
concretamente cuando dices "puedes cambiar la sintaxis y usar JOIN al estilo
Microsoft", me lo puedes aclarar y si es posible poner un ejemplo ?

Muchas gracias
Un saludo
Salvador Ramos
Murcia - España

[Microsoft MVP SQL Server / MCTS: SQL Server 2005]
www.helpdna.net (información sobre SQL Server y .NET)
Y ahora también en www.sqlserverymicrosoft.net

"Alejandro Mesa" escribió en el
mensaje news:

Hola Salvador,

Estas sentencias toman en cuenta multiples filas afectadas por una
sentencia
DML. Puedes cambiar la sintaxis y usar JOIN al estilo Microsoft.

UPDATE dbo.Articulos
SET Existencias = Existencias + (
select sum(d.Cantidad)
from deleted as d
where d.Articulo = dbo.Articulos.Codigo
)
where
exists (
select *
from deleted as d
where d.Articulo = dbo.Articulos.Codigo
)

UPDATE dbo.Articulos
SET Existencias = Existencias - (
select sum(i.Cantidad)
from inserted as i
where i.Articulo = dbo.Articulos.Codigo
)
where
exists (
select *
from inserted as i
where i.Articulo = dbo.Articulos.Codigo
)

AMB

"Salvador Ramos" wrote:

Hola a todos,

Estoy preparando un ejemplo didáctico para demostrar los problemas que
podemos tener si no utilizamos correctamente el código en nuestros
triggers,
básicamente es para demostrar que un trigger nos puede funcionar bien
para
actualizaciones (insert, update o delete) que afecten a una sola fila,
pero
cuando afecten a más de una fila nos puede fallar. Esa parte ya la tengo
hecha, ahora lo que quiero es modificar el trigger para que las
actualizaciones que afecten a más de una fila también funcionen
correctamente. He contemplado varias maneras de hacerlo, una es con una
tabla temporal. Lo que me gustaría, ya que es con un fin didactico, que
me
propusiesis el código del trigger que consideréis más optimo para
resolver
la situación.

Este es el código que tengo para el ejemlo, incluyendo las tablas y
diversas
actualizaciones:
USE Pruebas
DROP TABLE [dbo].[Articulos]
CREATE TABLE [dbo].[Articulos] (
[Codigo] [int] NOT NULL ,
[Nombre] [varchar] (30) COLLATE Modern_Spanish_CI_AS NOT NULL ,
[Existencias] [int] NOT NULL
) ON [PRIMARY]
DROP TABLE [dbo].[Ventas]
CREATE TABLE [dbo].[Ventas] (
[NumVenta] [int] IDENTITY (1, 1) NOT NULL ,
[Articulo] [int] NOT NULL ,
[Cantidad] [int] NOT NULL ,
[Precio] [int] NOT NULL
) ON [PRIMARY]
CREATE TRIGGER trg_ActExistencias ON Ventas
FOR INSERT, UPDATE, DELETE
as
UPDATE Articulos
SET Existencias = Existencias + Deleted.Cantidad
FROM Deleted inner join Articulos on Deleted.Articulo = Articulos.Codigo
UPDATE Articulos
SET Existencias = Existencias - Inserted.Cantidad
FROM Inserted inner join Articulos on Inserted.Articulo =
Articulos.Codigo

DELETE Articulos
DELETE Ventas
INSERT INTO Articulos(Codigo, Nombre, Existencias)
VALUES(1,'Tornillo 7mm',100)
INSERT INTO Articulos(Codigo, Nombre, Existencias)
VALUES(2,'Tornillo 10mm',200)
INSERT INTO Articulos(Codigo, Nombre, Existencias)
VALUES(3,'Tornillo 15mm',300)
SELECT * FROM Articulos
SELECT * FROM Ventas
INSERT INTO Ventas(Articulo, Cantidad, Precio)
VALUES(1,10,1000)
INSERT INTO Ventas(Articulo, Cantidad, Precio)
VALUES(2,12,1000)
INSERT INTO Ventas(Articulo, Cantidad, Precio)
VALUES(3,13,1000)
INSERT INTO Ventas(Articulo, Cantidad, Precio)
VALUES(1,20,1000)
SELECT * FROM Articulos
SELECT * FROM Ventas
UPDATE Ventas
SET Cantidad = Cantidad + 1
SELECT * FROM Articulos
UPDATE Ventas
SET Cantidad = Cantidad - 1
SELECT * FROM Articulos
DELETE Ventas
WHERE Articulo = 1
DELETE Ventas
WHERE Articulo = 2
DELETE Ventas
WHERE Articulo = 3
SELECT * FROM Articulos
SELECT * FROM Ventas

Espero vuestras sugerencias, muchas gracias.

Un saludo
Salvador Ramos
Murcia - España

[Microsoft MVP SQL Server / MCTS: SQL Server 2005]
www.helpdna.net (información sobre SQL Server y .NET)
Y ahora también en www.sqlserverymicrosoft.net

Responder a este mensaje

Ads by Google

Hacer una preguntaSiguiente

Tengo una respuesta

Busqueda sugerida