title: Historical Table in SQL Server
body: <p>I have two tables; the first named <code>PAYMENT</code> and the second is a historical table named <code>RecordPay</code>.</p>
  
  <p>I have two triggers, the first one is for insert in order to insert into the historical tables records from <code>Payment</code> table.</p>
  
  <p>Here is the code:</p>
  
  <pre><code>ALTER TRIGGER [dbo].[INSERT_HIST]
  ON  [dbo].[PAYMENT]
  FOR  INSERT
  AS 
  BEGIN
       DECLARE @User_op varchar(50) 
       DECLARE @RGNO varchar(50)
  
       DECLARE @PAYEUR varchar(50)
       DECLARE @DATESYS SMALLDATETIME 
       DECLARE @RG_DATE SMALLDATETIME 
       DECLARE @RG_Montant varchar(50)
  
       SELECT @User_op = cbUserName 
       FROM cbUserSession 
       WHERE cbSession = @@SPID
  
       SELECT @PAYEUR = CT_NumPayeur FROM INSERTED
  
       SELECT @DATESYS = GETDATE()
       SELECT @RG_Montant = RG_Montant FROM INSERTED
       SELECT @RG_DATE = RG_DATE FROM INSERTED
       SELECT @RGNO = RG_No FROM INSERTED
  
       INSERT INTO RecordPay (RG_NO, PAYEUR, CAISSIER, Montant, DATESYS,   DATECAI)
       VALUES (@RGNO, @PAYEUR, @user_op, @RG_Montant, @DATESYS, @RG_DATE)
  </code></pre>
  
  <p>This works well, my problem when I delete a row from <code>PAYMENT</code>, in <code>RecordPay</code> the record exists, and then when I insert another row in <code>PAYMENT</code> I had two <code>RG_NO</code> whith the same number. </p>
  
  <p>For example I insert a row in <code>PAYMENT</code> with RG_NO=1 then I deleted, and I create another row with RG_NO=2, in the recordPay (historical table) i get two lines with RG_NO=1.</p>
  
  <p>Here is the trigger for delete but it does not work</p>
  
  <pre><code> ALTER TRIGGER [dbo].[DEL_HIST]
   ON  [dbo].[PAYMENT]
   AFTER  DELETE
   AS 
   BEGIN
  
  
   DECLARE @User_op varchar(50) 
   DECLARE @RGNO varchar(50)
  
   DECLARE @PAYEUR varchar(50)
   DECLARE @DATESYS SMALLDATETIME 
   DECLARE @RG_DATE SMALLDATETIME 
   DECLARE @RG_Montant varchar(50)
  
  
   SELECT @PAYEUR = CT_NumPayeur FROM DELETED
  
   SELECT @RG_Montant = RG_Montant FROM DELETED
   SELECT @RG_DATE = RG_DATE FROM DELETED
  SELECT @RGNO = RG_No FROM DELETED
  
  DELETE FROM RECORDPAY  WHERE  
  RG_NO=@RGNO and PAYEUR= @PAYEUR  and CAISSIER=@user_op and Montant=@RG_Montant 
  END
  </code></pre>