1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103
| -- ================================================
-- Template generated from Template Explorer using:
-- Create Trigger (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- See additional Create Trigger templates for more
-- examples of different Trigger statements.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER dbo.tr_audit_email
ON dbo.email
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
declare @nbLDel int
declare @nbLIns int
select @nbLDel = count (*) from deleted
/*
idem que ci-dessus
select * from deleted
set @nbLDel = ROWCOUNT_BIG()
*/
select @nbLIns = count (*) from inserted
if @nbLDel = 0 and @nbLIns = 0
return;
-- on a fait un delete:
if @nbLIns = 0 -- and @nbLDel > 0 car sinon on serait déjà sorti
begin
insert into dbo.email_audit (
qui,
quoi,
quand,
em_id,
em_addr,
em_pe)
select
qui,
quoi,
quand,
del.em_id,
del.em_addr,
del.em_pe
from deleted as del;
-- on a fait un insert:
if @nbLDel = 0 and @nbLIns > 0
begin
insert into dbo.email_audit (
qui,
quoi,
quand,
em_id,
em_addr,
em_pe)
select
qui,
quoi,
quand,
ins.em_id,
ins.em_addr,
ins.em_pe
from inserted as ins;
END
voici mes tables:
email:
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP (1000) [em_id]
,[em_addr]
,[em_pe]
FROM [karaoke2].[dbo].[email]
email_audit:
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP (1000) [id]
,[qui]
,[quoi]
,[quand]
,[em_id]
,[em_addr]
,[em_pe]
FROM [karaoke2].[dbo].[email_audit] |
Partager