MS-SQL - trigger
/SQL/MS-SQL - trigger.sql
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'EMAIL_IF_CONFIRM_TRANSAC_DE_TYPE' AND type = 'TR')
DROP TRIGGER EMAIL_IF_CONFIRM_TRANSAC_DE_TYPE
GO
CREATE TRIGGER EMAIL_IF_CONFIRM_TRANSAC_DE_TYPE
ON transac
AFTER INSERT, UPDATE
AS
-- Constantes ----------------------
DECLARE @etat_reservation int
DECLARE @type_contact_bien_etre int
select @etat_reservation = 2;
select @type_contact_bien_etre = 83;
------------------------------------
DECLARE @et_id numeric
DECLARE @nb_transac_meme_ref_en_etat_reservation int
DECLARE @tc_id numeric
DECLARE @tr_id numeric
DECLARE @tr_reference varchar(30)
SELECT @et_id=et_id, @tc_id=tc_id, @tr_id=tr_id, @tr_reference=tr_reference
FROM Inserted inner join contact on contact.co_id = Inserted.co_id
IF @et_id = @etat_reservation AND @tc_id = @type_contact_bien_etre
BEGIN
select @nb_transac_meme_ref_en_etat_reservation = count(*) from transac
where tr_reference=@tr_reference and et_id = @etat_reservation;
IF @nb_transac_meme_ref_en_etat_reservation = 1 /* pas d'historique pour cette ref (dans le meme etat) */
BEGIN
insert into DOMI_rq(txt1, txt2) values(@tr_reference, @nb_transac_meme_ref_en_etat_reservation);
END
END
-------------------------------------------------
-- update transac set et_id=2 where tr_id=502372
select * from DOMI_rq
delete DOMI_rq