/* HA Plus 9.01,9.10,9.11,9.20,9.21,9.30,9.45,9.50 */
/* Version SQL Server                    */
/* Modifications dans la base HA Plus : ajout des triggers */

/*========================================================================*/
/*========   TRIGGERS DE MAJ DE DATEMODI ET DE LA TABLE TRANSACT   =======*/
/*========================================================================*/


/*===========TRIGGER SUR ARTICLES======*/

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'TRIG_ARTICLES_DATEMODI' AND type = 'TR')
   DROP TRIGGER TRIG_ARTICLES_DATEMODI
GO

CREATE TRIGGER TRIG_ARTICLES_DATEMODI
    ON ARTICLES 
    AFTER UPDATE,INSERT 
AS 
   UPDATE ARTICLES 
   SET    DATEMODI = 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),1,4) + 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),6,2) +
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),9,2)
   FROM   ARTICLES t 
   INNER JOIN INSERTED I 
   ON t.CODE_ART = I.CODE_ART  
GO


/*===========TRIGGER SUR FOURNISS======*/

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'TRIG_FOURNISS_DATEMODI' AND type = 'TR')
   DROP TRIGGER TRIG_FOURNISS_DATEMODI
GO

CREATE TRIGGER TRIG_FOURNISS_DATEMODI
    ON FOURNISS 
    AFTER UPDATE,INSERT 
AS 
   UPDATE FOURNISS 
   SET    DATEMODI = 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),1,4) + 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),6,2) +
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),9,2)
   FROM   FOURNISS t 
   INNER JOIN INSERTED I 
   ON t.CODE = I.CODE  
GO

/*===========TRIGGER SUR ARTFOURN======*/

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'TRIG_ARTFOURN_DATEMODI' AND type = 'TR')
   DROP TRIGGER TRIG_ARTFOURN_DATEMODI
GO

CREATE TRIGGER TRIG_ARTFOURN_DATEMODI
    ON ARTFOURN 
    AFTER UPDATE,INSERT 
AS 
   UPDATE ARTFOURN 
   SET    DATEMODI = 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),1,4) + 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),6,2) +
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),9,2)
   FROM   ARTFOURN t 
   INNER JOIN INSERTED I 
   ON t.CODE_ART = I.CODE_ART  
GO

/*===========TRIGGER SUR DA_ENTET======*/

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'TRIG_DA_ENTET_DATEMODI' AND type = 'TR')
   DROP TRIGGER TRIG_DA_ENTET_DATEMODI
GO

CREATE TRIGGER TRIG_DA_ENTET_DATEMODI
    ON DA_ENTET 
    AFTER UPDATE,INSERT,DELETE 
AS 

UPDATE DA_PLANN 
   SET    DATEMODI = 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),1,4) + 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),6,2) +
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),9,2)	   

FROM   DA_PLANN t 
   INNER JOIN INSERTED I 
   ON t.NO_DA = I.NO_DA 

DECLARE 
	@NO int
SELECT @NO = NO_DA FROM DELETED

IF NOT EXISTS(SELECT * FROM TRANSACT WHERE TYPEDOC = 'DA' AND TYPETRANS = 'DELETE' AND @NO=IDNUM1)
IF EXISTS(SELECT  NO_DA FROM DELETED)  
INSERT INTO TRANSACT (TYPEDOC,TYPETRANS,IDNUM1) VALUES ('DA','DELETE',@NO)
GO

/*===========TRIGGER SUR DA_LIGNE======*/

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'TRIG_DA_LIGNE_DATEMODI' AND type = 'TR')
   DROP TRIGGER TRIG_DA_LIGNE_DATEMODI
GO

CREATE TRIGGER TRIG_DA_LIGNE_DATEMODI
    ON DA_LIGNE 
    AFTER UPDATE,INSERT,DELETE 
AS 

UPDATE DA_PLANN 
   SET    DATEMODI = 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),1,4) + 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),6,2) +
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),9,2)	   

FROM   DA_PLANN t 
   INNER JOIN INSERTED I 
   ON t.NO_DA = I.NO_DA 

DECLARE 
	@NO int
SELECT @NO = NO_DA FROM DELETED

IF NOT EXISTS(SELECT * FROM TRANSACT WHERE TYPEDOC = 'DA' AND TYPETRANS = 'DELETE' AND @NO=IDNUM1)
IF EXISTS(SELECT  NO_DA FROM DELETED)  
INSERT INTO TRANSACT (TYPEDOC,TYPETRANS,IDNUM1) VALUES ('DA','DELETE',@NO)
GO

/*===========TRIGGER SUR DA_PLANN======*/

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'TRIG_DA_PLANN_DATEMODI' AND type = 'TR')
   DROP TRIGGER TRIG_DA_PLANN_DATEMODI
GO

CREATE TRIGGER TRIG_DA_PLANN_DATEMODI
    ON DA_PLANN 
    AFTER UPDATE,INSERT,DELETE 
AS 

UPDATE DA_PLANN 
   SET    DATEMODI = 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),1,4) + 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),6,2) +
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),9,2)	   

FROM   DA_PLANN t 
   INNER JOIN INSERTED I 
   ON t.NO_DA = I.NO_DA 

DECLARE 
	@NO int
SELECT @NO = NO_DA FROM DELETED

IF NOT EXISTS(SELECT * FROM TRANSACT WHERE TYPEDOC = 'DA' AND TYPETRANS = 'DELETE' AND @NO=IDNUM1)
IF EXISTS(SELECT  NO_DA FROM DELETED)  
INSERT INTO TRANSACT (TYPEDOC,TYPETRANS,IDNUM1) VALUES ('DA','DELETE',@NO)
GO 

/*===========TRIGGER SUR CA_ENTET======*/

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'TRIG_CA_ENTET_DATEMODI' AND type = 'TR')
   DROP TRIGGER TRIG_CA_ENTET_DATEMODI
GO

CREATE TRIGGER TRIG_CA_ENTET_DATEMODI
    ON CA_ENTET 
    AFTER UPDATE,INSERT,DELETE 
AS 

UPDATE CA_PL_CT
   SET    DATEMODI = 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),1,4) + 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),6,2) +
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),9,2)	   

FROM   CA_PL_CT t 
   INNER JOIN INSERTED I 
   ON t.NO_CA = I.NO_CDE 

DECLARE 
	@NO int
SELECT @NO = NO_CDE FROM DELETED

IF NOT EXISTS(SELECT * FROM TRANSACT WHERE TYPEDOC = 'CA' AND TYPETRANS = 'DELETE' AND @NO=IDNUM1)
IF EXISTS(SELECT  NO_CDE FROM DELETED)  
INSERT INTO TRANSACT (TYPEDOC,TYPETRANS,IDNUM1) VALUES ('CA','DELETE',@NO)
GO

/*===========TRIGGER SUR CA_LIGNE======*/

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'TRIG_CA_LIGNE_DATEMODI' AND type = 'TR')
   DROP TRIGGER TRIG_CA_LIGNE_DATEMODI
GO

CREATE TRIGGER TRIG_CA_LIGNE_DATEMODI
    ON CA_LIGNE 
    AFTER UPDATE,INSERT,DELETE 
AS 

UPDATE CA_PL_CT 
   SET    DATEMODI = 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),1,4) + 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),6,2) +
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),9,2)	   

FROM   CA_PL_CT t 
   INNER JOIN INSERTED I 
   ON t.NO_CA = I.NO_CDE 

DECLARE 
	@NO int
SELECT @NO = NO_CDE FROM DELETED

IF NOT EXISTS(SELECT * FROM TRANSACT WHERE TYPEDOC = 'CA' AND TYPETRANS = 'DELETE' AND @NO=IDNUM1)
IF EXISTS(SELECT  NO_CDE FROM DELETED)  
INSERT INTO TRANSACT (TYPEDOC,TYPETRANS,IDNUM1) VALUES ('CA','DELETE',@NO)
GO


/*===========TRIGGER SUR CA_PL_CT======*/

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'TRIG_CA_PL_CT_DATEMODI' AND type = 'TR')
   DROP TRIGGER TRIG_CA_PL_CT_DATEMODI
GO

CREATE TRIGGER TRIG_CA_PL_CT_DATEMODI
    ON CA_PL_CT 
    AFTER UPDATE,INSERT,DELETE 
AS 

UPDATE CA_PL_CT  
   SET    DATEMODI = 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),1,4) + 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),6,2) +
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),9,2)	   

FROM   CA_PL_CT t 
   INNER JOIN INSERTED I 
   ON t.NO_CA = I.NO_CA 

DECLARE 
	@NO int
SELECT @NO = NO_CA FROM DELETED

IF NOT EXISTS(SELECT * FROM TRANSACT WHERE TYPEDOC = 'CA' AND TYPETRANS = 'DELETE'AND @NO=IDNUM1)
IF EXISTS(SELECT  NO_CA FROM DELETED)  
INSERT INTO TRANSACT (TYPEDOC,TYPETRANS,IDNUM1) VALUES ('CA','DELETE',@NO)
GO 


/*===========TRIGGER SUR HCA_ENTE======*/

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'TRIG_HCA_ENTE_DATEMODI' AND type = 'TR')
   DROP TRIGGER TRIG_HCA_ENTE_DATEMODI
GO

CREATE TRIGGER TRIG_HCA_ENTE_DATEMODI
    ON HCA_ENTE 
    AFTER UPDATE,INSERT,DELETE 
AS 

UPDATE HCA_PLCT
   SET    DATEMODI = 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),1,4) + 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),6,2) +
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),9,2)	   

FROM   HCA_PLCT t 
   INNER JOIN INSERTED I 
   ON t.NUM_CDE = I.NO_CDE 

DECLARE 
	@NO int
SELECT @NO = NO_CDE FROM DELETED

IF NOT EXISTS(SELECT * FROM TRANSACT WHERE TYPEDOC = 'CA' AND TYPETRANS = 'DELETE' AND @NO=IDNUM1)
IF EXISTS(SELECT  NO_CDE FROM DELETED)  
INSERT INTO TRANSACT (TYPEDOC,TYPETRANS,IDNUM1) VALUES ('CA','DELETE',@NO)
GO

/*===========TRIGGER SUR HCA_LIGN======*/

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'TRIG_HCA_LIGN_DATEMODI' AND type = 'TR')
   DROP TRIGGER TRIG_HCA_LIGN_DATEMODI
GO

CREATE TRIGGER TRIG_HCA_LIGN_DATEMODI
    ON HCA_LIGN 
    AFTER UPDATE,INSERT,DELETE 
AS 

UPDATE HCA_PLCT 
   SET    DATEMODI = 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),1,4) + 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),6,2) +
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),9,2)	   

FROM   HCA_PLCT t 
   INNER JOIN INSERTED I 
   ON t.NUM_CDE = I.NUMCDE 

DECLARE 
	@NO int
SELECT @NO = NUMCDE FROM DELETED

IF NOT EXISTS(SELECT * FROM TRANSACT WHERE TYPEDOC = 'CA' AND TYPETRANS = 'DELETE' AND @NO=IDNUM1)
IF EXISTS(SELECT  NUMCDE FROM DELETED)  
INSERT INTO TRANSACT (TYPEDOC,TYPETRANS,IDNUM1) VALUES ('CA','DELETE',@NO)
GO


/*===========TRIGGER SUR HCA_PLCT======*/

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'TRIG_HCA_PLCT_DATEMODI' AND type = 'TR')
   DROP TRIGGER TRIG_HCA_PLCT_DATEMODI
GO

CREATE TRIGGER TRIG_HCA_PLCT_DATEMODI
    ON HCA_PLCT 
    AFTER UPDATE,INSERT,DELETE 
AS 

UPDATE HCA_PLCT  
   SET    DATEMODI = 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),1,4) + 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),6,2) +
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),9,2)	   

FROM   HCA_PLCT t 
   INNER JOIN INSERTED I 
   ON t.NUM_CDE = I.NUM_CDE 

DECLARE 
	@NO int
SELECT @NO = NUM_CDE FROM DELETED

IF NOT EXISTS(SELECT * FROM TRANSACT WHERE TYPEDOC = 'CA' AND TYPETRANS = 'DELETE'AND @NO=IDNUM1)
IF EXISTS(SELECT  NUM_CDE FROM DELETED)  
INSERT INTO TRANSACT (TYPEDOC,TYPETRANS,IDNUM1) VALUES ('CA','DELETE',@NO)
GO 


/*===========TRIGGER SUR BR_RECEP======*/

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'TRIG_BR_RECEP_DATEMODI' AND type = 'TR')
   DROP TRIGGER TRIG_BR_RECEP_DATEMODI
GO

CREATE TRIGGER TRIG_BR_RECEP_DATEMODI
    ON BR_RECEP 
    AFTER UPDATE,INSERT,DELETE 
AS 

UPDATE BR_LIGNE 
   SET    DATEMODI = 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),1,4) + 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),6,2) +
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),9,2)	   

FROM   BR_LIGNE t 
   INNER JOIN INSERTED I 
   ON t.NUM_BR = I.NUM_BR 

DECLARE 
	@NO int
SELECT @NO = NUM_BR FROM DELETED

IF NOT EXISTS(SELECT * FROM TRANSACT WHERE TYPEDOC = 'BR' AND TYPETRANS = 'DELETE' AND @NO=IDNUM1)
IF EXISTS(SELECT  NUM_BR FROM DELETED)  
INSERT INTO TRANSACT (TYPEDOC,TYPETRANS,IDNUM1) VALUES ('BR','DELETE',@NO)
GO


/*===========TRIGGER SUR BR_LIGNE======*/

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'TRIG_BR_LIGNE_DATEMODI' AND type = 'TR')
   DROP TRIGGER TRIG_BR_LIGNE_DATEMODI
GO

CREATE TRIGGER TRIG_BR_LIGNE_DATEMODI
    ON BR_LIGNE 
    AFTER UPDATE,INSERT,DELETE 
AS 

UPDATE BR_LIGNE 
   SET    DATEMODI = 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),1,4) + 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),6,2) +
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),9,2)	   

FROM   BR_LIGNE t 
   INNER JOIN INSERTED I 
   ON t.NUM_BR = I.NUM_BR 

DECLARE 
	@NO int
SELECT @NO = NUM_BR FROM DELETED

IF NOT EXISTS(SELECT * FROM TRANSACT WHERE TYPEDOC = 'BR' AND TYPETRANS = 'DELETE' AND @NO=IDNUM1)
IF EXISTS(SELECT  NUM_BR FROM DELETED)  
INSERT INTO TRANSACT (TYPEDOC,TYPETRANS,IDNUM1) VALUES ('BR','DELETE',@NO)
GO


/*===========TRIGGER SUR FAC_ENT======*/

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'TRIG_FAC_ENT_DATEMODI' AND type = 'TR')
   DROP TRIGGER TRIG_FAC_ENT_DATEMODI
GO

CREATE TRIGGER TRIG_FAC_ENT_DATEMODI
    ON FAC_ENT 
    AFTER UPDATE,INSERT,DELETE 
AS 

UPDATE FAC_LIG 
   SET    DATEMODI = 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),1,4) + 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),6,2) +
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),9,2)	   

FROM   FAC_LIG t 
   INNER JOIN INSERTED I 
   ON t.NUM_FACT = I.NUM_FACT 

DECLARE 
	@NO int
SELECT @NO = NUM_FACT FROM DELETED

IF NOT EXISTS(SELECT * FROM TRANSACT WHERE TYPEDOC = 'FAC' AND TYPETRANS = 'DELETE' AND @NO=IDNUM1)
IF EXISTS(SELECT  NUM_FACT FROM DELETED)  
INSERT INTO TRANSACT (TYPEDOC,TYPETRANS,IDNUM1) VALUES ('FAC','DELETE',@NO)
GO

/*===========TRIGGER SUR FAC_LIG======*/

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'TRIG_FAC_LIG_DATEMODI' AND type = 'TR')
   DROP TRIGGER TRIG_FAC_LIG_DATEMODI
GO

CREATE TRIGGER TRIG_FAC_LIG_DATEMODI
    ON FAC_LIG 
    AFTER UPDATE,INSERT,DELETE 
AS 

UPDATE FAC_LIG 
   SET    DATEMODI = 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),1,4) + 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),6,2) +
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),9,2)	   

FROM   FAC_LIG t 
   INNER JOIN INSERTED I 
   ON t.NUM_FACT = I.NUM_FACT 

DECLARE 
	@NO int
SELECT @NO = NUM_FACT FROM DELETED

IF NOT EXISTS(SELECT * FROM TRANSACT WHERE TYPEDOC = 'FAC' AND TYPETRANS = 'DELETE' AND @NO=IDNUM1)
IF EXISTS(SELECT  NUM_FACT FROM DELETED)  
INSERT INTO TRANSACT (TYPEDOC,TYPETRANS,IDNUM1) VALUES ('FAC','DELETE',@NO)
GO


/*===========TRIGGER SUR EXPENTET======*/

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'TRIG_EXPENTET_DATEMODI' AND type = 'TR')
   DROP TRIGGER TRIG_EXPENTET_DATEMODI
GO

CREATE TRIGGER TRIG_EXPENTET_DATEMODI
    ON EXPENTET 
    AFTER UPDATE,INSERT,DELETE 
AS 

UPDATE DETCOLIS
   SET    DATEMODI = 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),1,4) + 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),6,2) +
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),9,2)	   

FROM   DETCOLIS t 
   INNER JOIN INSERTED I 
   ON t.NUM_EXP = I.NUM_EXP 

DECLARE 
	@NO int
SELECT @NO = NUM_EXP FROM DELETED

IF NOT EXISTS(SELECT * FROM TRANSACT WHERE TYPEDOC = 'EXP' AND TYPETRANS = 'DELETE' AND @NO=IDNUM1)
IF EXISTS(SELECT  NUM_EXP FROM DELETED)  
INSERT INTO TRANSACT (TYPEDOC,TYPETRANS,IDNUM1) VALUES ('EXP','DELETE',@NO)
GO


/*===========TRIGGER SUR EXPCOLIS======*/

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'TRIG_EXPCOLIS_DATEMODI' AND type = 'TR')
   DROP TRIGGER TRIG_EXPCOLIS_DATEMODI
GO

CREATE TRIGGER TRIG_EXPCOLIS_DATEMODI
    ON EXPCOLIS 
    AFTER UPDATE,INSERT,DELETE 
AS 

UPDATE DETCOLIS
   SET    DATEMODI = 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),1,4) + 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),6,2) +
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),9,2)	   

FROM   DETCOLIS t 
   INNER JOIN INSERTED I 
   ON t.NUM_EXP = I.NUM_EXP 

DECLARE 
	@NO int
SELECT @NO = NUM_EXP FROM DELETED

IF NOT EXISTS(SELECT * FROM TRANSACT WHERE TYPEDOC = 'EXP' AND TYPETRANS = 'DELETE' AND @NO=IDNUM1)
IF EXISTS(SELECT  NUM_EXP FROM DELETED)  
INSERT INTO TRANSACT (TYPEDOC,TYPETRANS,IDNUM1) VALUES ('EXP','DELETE',@NO)
GO


/*===========TRIGGER SUR DETCOLIS======*/

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'TRIG_DETCOLIS_DATEMODI' AND type = 'TR')
   DROP TRIGGER TRIG_DETCOLIS_DATEMODI
GO

CREATE TRIGGER TRIG_DETCOLIS_DATEMODI
    ON DETCOLIS 
    AFTER UPDATE,INSERT,DELETE 
AS 

UPDATE DETCOLIS
   SET    DATEMODI = 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),1,4) + 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),6,2) +
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),9,2)	   

FROM   DETCOLIS t 
   INNER JOIN INSERTED I 
   ON t.NUM_EXP = I.NUM_EXP 

DECLARE 
	@NO int
SELECT @NO = NUM_EXP FROM DELETED

IF NOT EXISTS(SELECT * FROM TRANSACT WHERE TYPEDOC = 'EXP' AND TYPETRANS = 'DELETE' AND @NO=IDNUM1)
IF EXISTS(SELECT  NUM_EXP FROM DELETED)  
INSERT INTO TRANSACT (TYPEDOC,TYPETRANS,IDNUM1) VALUES ('EXP','DELETE',@NO)
GO


/*===========TRIGGER SUR SARTICLE (Stock)======*/

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'TRIG_SARTICLE_DATEMODI' AND type = 'TR')
   DROP TRIGGER TRIG_SARTICLE_DATEMODI
GO

CREATE TRIGGER TRIG_SARTICLE_DATEMODI
    ON SARTICLE 
    AFTER UPDATE,INSERT 
AS 
   UPDATE SARTICLE 
   SET    DATEMODI = 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),1,4) + 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),6,2) +
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),9,2)
   FROM   SARTICLE t 
   INNER JOIN INSERTED I 
   ON t.CODE_ART = I.CODE_ART  
GO

/*===========TRIGGER SUR SSTOCKLO (Stock Local)======*/

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'TRIG_SSTOCKLO_DATEMODI' AND type = 'TR')
   DROP TRIGGER TRIG_SSTOCKLO_DATEMODI
GO

CREATE TRIGGER TRIG_SSTOCKLO_DATEMODI
    ON SSTOCKLO 
    AFTER UPDATE,INSERT 
AS 
   UPDATE SSTOCKLO 
   SET    DATEMODI = 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),1,4) + 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),6,2) +
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),9,2)
   FROM   SSTOCKLO t 
   INNER JOIN INSERTED I 
   ON ( t.CODE_ART = I.CODE_ART AND t.SITE = I.SITE )
GO


/*===========TRIGGER SUR SMOUVEME (Mouvements)=*/

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'TRIG_SMOUVEME_DATEMODI' AND type = 'TR')
   DROP TRIGGER TRIG_SMOUVEME_DATEMODI
GO

CREATE TRIGGER TRIG_SMOUVEME_DATEMODI
    ON SMOUVEME 
    AFTER UPDATE,INSERT 
AS 
   UPDATE SMOUVEME 
   SET    DATEMODI = 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),1,4) + 
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),6,2) +
substring(CONVERT (CHAR(10),CURRENT_TIMESTAMP,126  ),9,2)
   FROM   SMOUVEME t 
   INNER JOIN INSERTED I 
   ON t.NO_MVT = I.NO_MVT  
GO

