August 24, 2005 at 7:42 am
What's the best wat to do this? I need to audit changes to tables. I'm thinking trigger but I'm not quite sure how I do it. Heres what I need
A standard table that looks like this.
Tbl_XYZ
Fname
Lname
Guid
Address
I need to create a second table for each existing table that looks like this
Tbl_TX_XYZ
Fname
Lname
GUID
ADDRESS
IUD (Inserted updated deleted)
Update date (time date)
Anyway, everytime tbl_xyz is updated, I need a trigger to copy the information inserted/updated/deleted to tbl_tx_xyx, designate if transaction was inserted updates deleted and add a time stamp. Can anyone tell mr if this is possible or if there might be a better way to do the exact same thing?
August 24, 2005 at 8:10 am
This trigger does more than auditing, but you'll get the idea :
CREATE TRIGGER [UpdateSommePiece_AuditEP] ON [dbo].[EMPLACEMENT Pièce]
FOR INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON
Declare @iCountIns as int
Declare @iCountDel as int
UPDATE P
SET P.[Qtée total] = dtNouvellesQte.QteTotal
, P.[Qtée magasin] = dtNouvellesQte.QteMagasin
FROM dbo.PIECES P INNER JOIN
(
SELECT
EP.[No de produit]
, SUM(EP.Qtée) AS QteTotal
, SUM(CASE WHEN EP.Emplacement = 'EP' THEN EP.Qtée ELSE 0 END) AS QteMagasin
FROM dbo.[Emplacement Pièce] EP inner join dbo.PIECES P on P.[No de produit] = EP.[No de produit] INNER JOIN
(SELECT ISNULL(D.[No de produit], I.[No de produit]) AS [No de produit] FROM Inserted I FULL OUTER JOIN Deleted D ON I.[No de produit] = D.[No de produit]) dtModified ON EP.[No de produit] = dtModified.[No de produit]
GROUP BY EP.[No de produit]
) dtNouvellesQte ON dtNouvellesQte.[No de produit] = P.[No de produit]
SET @iCountIns = (Select count(*) from Inserted)
SET @iCountDel = (Select count(*) from Deleted)
if @iCountDel > @iCountIns
begin
--delete query
--make sure there's at least one row remaining for 'EP'
Insert into dbo.[Emplacement Pièce] ([No de produit], Emplacement, Qtée, [Qtée recom])
SELECT dbo.PIECES.[No de produit], 'EP' as Emplacement, 0 as Qtée, 0 as [Qtée recom]
FROM dbo.PIECES inner join Deleted Del on dbo.PIECES.[no de produit] = Del.[No de produit]
WHERE NOT EXISTS (SELECT * FROM dbo.[EMPLACEMENT Pièce] EP
WHERE dbo.PIECES.[No de produit] = EP.[No de produit] AND Emplacement = 'EP')
Insert into dbo.AuditEP (D.TypeModif, [No de produit], OldEmplacement, OldQtée, [OldQtée recom], NewEmplacement, NewQtée, [NewQtée recom], DiffQte, QteInventaire, QteStockRoom)
(Select 'Delete' as TypeModif, [No de produit], Emplacement, Qtée, [Qtée recom], null as NewEmplacement, null as NewQtée, null as [NewQtée recom], -1 * Qtée as DiffQte,
isnull((Select [Qtée total] from dbo.PIECES P where P.[No de produit] = D.[no de produit]), 0) as QteInventaire,
(Select Qtée from dbo.[Emplacement Pièce] EP where D.[No de produit] = EP.[No de produit] and EP.Emplacement = 'EP') as QteStockRoom from Deleted D)
end
else if @iCountIns > @iCountDel
begin
--Insert query
Insert into dbo.AuditEP (D.TypeModif, [No de produit], OldEmplacement, OldQtée, [OldQtée recom], NewEmplacement, NewQtée, [NewQtée recom], DiffQte, QteInventaire, QteStockRoom)
(Select 'Insert' as TypeModif, [No de produit], null as OldEmplacement, null as OldQtée, null as [OldQtée recom], Emplacement as NewEmplacement, Qtée as NewQtée, [Qtée recom] as [NewQtée recom], Qtée as DiffQte,
isnull((Select [Qtée total] from dbo.PIECES P where P.[No de produit] = Ins.[no de produit]), 0) as QteInventaire,
(Select Qtée from dbo.[Emplacement Pièce] EP where Ins.[No de produit] = EP.[No de produit] and EP.Emplacement = 'EP') as QteStockRoom from Inserted Ins)
end
else
begin
--Update query
Insert into dbo.AuditEP (D.TypeModif, [No de produit], OldEmplacement, OldQtée, [OldQtée recom], NewEmplacement, NewQtée, [NewQtée recom], DiffQte, QteInventaire, QteStockRoom)
(Select 'Update' as TypeModif, D.[No de produit], D.Emplacement, D.Qtée, D.[Qtée recom], Ins.Emplacement, Ins.Qtée, Ins.[Qtée recom], Ins.Qtée - D.Qtée as DiffQte,
isnull((Select [Qtée total] from dbo.PIECES P where P.[No de produit] = Ins.[no de produit]), 0),
(Select Qtée from dbo.[Emplacement Pièce] EP where Ins.[No de produit] = EP.[No de produit] and EP.Emplacement = 'EP') as QteStockRoom from Deleted D inner join Inserted Ins on Ins.[No de produit] = D.[No de produit] AND Ins.Emplacement = D.Emplacement)
end
SET NOCOUNT OFF
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply