Insert/Update to another table using trigger

1
votes

I have 2 tables, tableA and tableB.

tableA:
id     |     status      |     Flag     |   ResultOfAction   |   ExternalId |  Customer
1             200               2                 0                332            C1
2             200               0                 0                333            C1

If old.status <> new.status OR Flag = 3 OR ResultofAction = 1, the trigger will populate another table.

e.g

tableB:
id     |     status      |     Flag     |   ResultOfAction   |   ExternalId | tableAId
1             200               2                 0                332           1

if tableA has new insertion: 

tableA:
id     |     status      |     Flag     |   ResultOfAction   |   ExternalId |  Customer
1             200               2                 0                332            C1
2             200               0                 0                333            C1
3             200               2                 0                334            C1

TableB will populate new row automatically.

tableB:
id     |     status      |     Flag     |   ResultOfAction   |   ExternalId | tableAId
1             200               2                 0                332           1
2             200               2                 0                334           3

If one of the row in tableA update. e.g. id of 1 to 300, then tableB

tableB:
id     |     status      |     Flag     |   ResultOfAction   |   ExternalId  | tableAId
1             300               2                 0                332           1
2             200               2                 0                334           3

Here is my exising Trigger. Anyone can help?

CREATE OR REPLACE TRIGGER TABLEA_TRG AFTER INSERT OR UPDATE ON TABLEA
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW WHEN (OLD.STATUS <> NEW.STATUS OR NEW.FLAG = 3 or NEW.RESULTOFACTION = 1)
BEGIN 
IF INSERTING THEN
  INSERT INTO TABLEB(TABLEAID, ExternalId, STATUS, Flag)
    SELECT :NEW.ID, :NEW.ExternalId, :NEW.STATUS, :NEW.FLAG FROM DUAL
      WHEN NOT EXISTS (SELECT 1 FROM TABLEB WHERE tableAId = :NEW.id);
ELSIF UPDATING THEN
  IF :NEW.STATUS <> :OLD.STATUS THEN
    UPDATE TABLEB DWT SET DWT.tableAId = :NEW.id, DWT.ExternalId = :NEW.ExternalId,
      DWT.STATUS = :NEW.STATUS, DWT.Flag = :NEW.Flag;
  END IF;
  MERGE INTO TABLEB D
  USING DUAL ON (D.TABLEAID = :NEW.ID)
  WHEN MATCHED THEN 
  UPDATE SET D.STATUS = :NEW.STATUS
  WHEN NOT MATCHED THEN
  INSERT (D.TABLEAID, D.ExternalId, D.STATUS, D.FLAG) VALUE (:NEW.ID, :NEW.ExternalId, :NEW.STATUS, :NEW.FLAG);
END IF;
END;
/

There are errors * Error(7,7): PL/SQL: SQL Statement ignored * Error(9,16): PL/SQL: ORA-00933: SQL command not properly ended

This is my first time doing Trigger in Oracle, can someone help me please?

5
oracletriggers
If TableA is inserting then write an insert statement else write an update statement. What is so hard about that? - APC
@APC, first time, not very sure about syntax of Trigger. Find those source code on the web. - king jia

5 Answers

1
votes

check this out:

CREATE OR REPLACE TRIGGER TABLEA_TRG AFTER INSERT OR UPDATE ON TABLEA
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW WHEN (OLD.STATUS <> NEW.STATUS OR NEW.FLAG = 3 or NEW.RESULTOFACTION
    begin
      IF INSERTING THEN
        begin
        INSERT INTO TABLEB
          (TABLEAID, ExternalId, STATUS, Flag) values
          (:new.id, :new.externalId, :new.status, :new.flag);
        -- assuming, there is an unique key on id
        exception
          when dup_val_on_index then
            null;
        end;

      ELSIF UPDATING THEN
        IF :NEW.STATUS <> :OLD.STATUS THEN
          UPDATE TABLEB DWT
             SET DWT.tableAId   = :NEW.id,
                 DWT.ExternalId = :NEW.ExternalId,
                 DWT.STATUS     = :NEW.STATUS,
                 DWT.Flag       = :NEW.Flag;
        END IF;

        MERGE INTO TABLEB D
        USING DUAL
        ON (D.TABLEAID = :NEW.ID)
        WHEN MATCHED THEN
          UPDATE SET D.STATUS = :NEW.STATUS
        WHEN NOT MATCHED THEN
          INSERT
            (D.TABLEAID, D.ExternalId, D.STATUS, D.FLAG) VALUEs
            (:NEW.ID, :NEW.ExternalId, :NEW.STATUS, :NEW.FLAG);
      END IF;

    end test;
0
votes

I don't really got the problem but here is an example:

I've got a TABLE_A and an auditing table (TABLE_A_AUDIT) which contains all operations on TABLE_A. Therefor I have three triggers, one for inserting, one for updating and one for delete.

create table TABLE_A
(
  key_value VARCHAR2(20),
  value     VARCHAR2(20)
)

create table TABLE_A_AUDIT
(
  audit_timestamp TIMESTAMP(6),
  audit_action    VARCHAR2(1),
  key_value       VARCHAR2(20),
  value           VARCHAR2(20)
)

Insert Trigger:

create or replace trigger table_a_insert before insert on table_a for each row
begin
  insert into table_a_audit
    (audit_timestamp, audit_action, key_value, value)
  values
    (systimestamp, 'I', :new.key_value, :new.value);
end;

Update Trigger:

create or replace trigger table_a_update before update on table_a for each row
begin
  insert into table_a_audit
    (audit_timestamp, audit_action, key_value, value)
  values
    (systimestamp, 'U', :new.key_value, :new.value);
end;

Delete Trigger:

create or replace trigger table_a_delete before delete on table_a for each row
begin
  insert into table_a_audit
    (audit_timestamp, audit_action, key_value, value)
  values
    (systimestamp, 'D', :old.key_value, :old.value);
end;

I hope this example helps.

0
votes

you cannot select :new.id, etc. from the DUAL table. This table only have the column "DUMMY". I'm not sure what you want, but I think you should use a sequence to get a new ID ...

0
votes
USE [SocRamala]
GO
/****** Object:  Trigger [dbo].[tgr_LOGMILLPUR]    Script Date: 8/1/2018 1:56:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[tgr_LOGMILLPUR] on [dbo].[MILL_PUR]    
after UPDATE, INSERT, DELETE    
as    
if exists(SELECT * from inserted) and exists (SELECT * from deleted)    
begin          
    INSERT into LOG_MILLPUR(M_NUMBER, M_DATE, M_CENTRE, M_Alt_Cen, M_SOC, M_MODE, M_VILL, M_GROW, M_CATEG, M_VAR, M_GROSS, M_TARE, M_JOONA, M_FinalWt, M_AMOUNT, M_SMP_AMT, M_ADDI_AMT, M_CounterNo, M_GrossSlipNo, M_BillNo, M_HologramNo, M_TROLLYNO, M_CANE_TYPE, M_CANE_QUAL, M_SH_SUP_Wt, M_EXC_SUP_Wt, M_PAY_LOCK, M_Pay_Lock_Dt, M_Pay_Lock_opr, M_TOK_NO, M_TOK_DT, M_TOK_TM, M_TOK_OPR, M_TOK_SHIFT, M_TOK_COUNTERNO, M_TOK_KANTANO, M_TOK_IMAGE, M_GROS_DT, M_GROS_OPR, M_GROS_SHIFT, M_GROS_COUNTERNO, M_GROS_FIMAGE, M_GROS_BIMAGE, M_CardNo, M_TARE_DT, M_TARE_OPR, M_SHIFT, M_IND_NO, M_IND_DT, M_IND_CN, M_W_CLERK, M_ADV_AMT, M_ADV_DT, M_SYS_DT, M_SYS_TM, M_CASHIER, M_SCROLL, m_adnl_amt, Token_no, M_REMARK, M_HHT, M_CENTRE_HHT, m_adv_Amt20, M_REMARK2, M_G_UNIQUE_NO,M_BILLA_NO, m_hologram_no, m_adv_paid, M_FImagName, M_BImagName, M_PAYABLE, M_BD_CD, M_PurDate, ID,M_reprint, M_cr_by, m_cr_date,  LOG_STATUS, LOG_DATE,LOG_ACTIVITY,              LOG_IP,                                                        LOG_SQLUSER,                                 LOG_PCNAME)                          
                     Select M_NUMBER, M_DATE, M_CENTRE, M_Alt_Cen, M_SOC, M_MODE, M_VILL, M_GROW, M_CATEG, M_VAR, M_GROSS, M_TARE, M_JOONA, M_FinalWt, M_AMOUNT, M_SMP_AMT, M_ADDI_AMT, M_CounterNo, M_GrossSlipNo, M_BillNo, M_HologramNo, M_TROLLYNO, M_CANE_TYPE, M_CANE_QUAL, M_SH_SUP_Wt, M_EXC_SUP_Wt, M_PAY_LOCK, M_Pay_Lock_Dt, M_Pay_Lock_opr, M_TOK_NO, M_TOK_DT, M_TOK_TM, M_TOK_OPR, M_TOK_SHIFT, M_TOK_COUNTERNO, M_TOK_KANTANO, M_TOK_IMAGE, M_GROS_DT, M_GROS_OPR, M_GROS_SHIFT, M_GROS_COUNTERNO, M_GROS_FIMAGE, M_GROS_BIMAGE, M_CardNo, M_TARE_DT, M_TARE_OPR, M_SHIFT, M_IND_NO, M_IND_DT, M_IND_CN, M_W_CLERK, M_ADV_AMT, M_ADV_DT, M_SYS_DT, M_SYS_TM, M_CASHIER, M_SCROLL, m_adnl_amt, Token_no, M_REMARK, M_HHT, M_CENTRE_HHT, m_adv_Amt20, M_REMARK2, M_G_UNIQUE_NO, M_BILLA_NO, m_hologram_no, m_adv_paid, M_FImagName, M_BImagName, M_PAYABLE, M_BD_CD, M_PurDate, ID,M_reprint, M_cr_by, m_cr_date,       'NEW', GETDATE(),   'UPDATE',CONVERT(NVARCHAR,ISNULL(CONNECTIONPROPERTY('client_net_address'),'')),CONVERT(NVARCHAR,ISNULL(SUSER_SNAME(),'')),CONVERT(NVARCHAR,ISNULL(HOST_NAME(),'')) from  inserted i ;    
    INSERT into LOG_MILLPUR(M_NUMBER, M_DATE, M_CENTRE, M_Alt_Cen, M_SOC, M_MODE, M_VILL, M_GROW, M_CATEG, M_VAR, M_GROSS, M_TARE, M_JOONA, M_FinalWt, M_AMOUNT, M_SMP_AMT, M_ADDI_AMT, M_CounterNo, M_GrossSlipNo, M_BillNo, M_HologramNo, M_TROLLYNO, M_CANE_TYPE, M_CANE_QUAL, M_SH_SUP_Wt, M_EXC_SUP_Wt, M_PAY_LOCK, M_Pay_Lock_Dt, M_Pay_Lock_opr, M_TOK_NO, M_TOK_DT, M_TOK_TM, M_TOK_OPR, M_TOK_SHIFT, M_TOK_COUNTERNO, M_TOK_KANTANO, M_TOK_IMAGE, M_GROS_DT, M_GROS_OPR, M_GROS_SHIFT, M_GROS_COUNTERNO, M_GROS_FIMAGE, M_GROS_BIMAGE, M_CardNo, M_TARE_DT, M_TARE_OPR, M_SHIFT, M_IND_NO, M_IND_DT, M_IND_CN, M_W_CLERK, M_ADV_AMT, M_ADV_DT, M_SYS_DT, M_SYS_TM, M_CASHIER, M_SCROLL, m_adnl_amt, Token_no, M_REMARK, M_HHT, M_CENTRE_HHT, m_adv_Amt20, M_REMARK2, M_G_UNIQUE_NO, M_BILLA_NO, m_hologram_no, m_adv_paid, M_FImagName, M_BImagName, M_PAYABLE, M_BD_CD, M_PurDate, ID,M_reprint, M_cr_by, m_cr_date,  LOG_STATUS, LOG_DATE,LOG_ACTIVITY,             LOG_IP,                                                        LOG_SQLUSER,                                  LOG_PCNAME)                          
                     Select M_NUMBER, M_DATE, M_CENTRE, M_Alt_Cen, M_SOC, M_MODE, M_VILL, M_GROW, M_CATEG, M_VAR, M_GROSS, M_TARE, M_JOONA, M_FinalWt, M_AMOUNT, M_SMP_AMT, M_ADDI_AMT, M_CounterNo, M_GrossSlipNo, M_BillNo, M_HologramNo, M_TROLLYNO, M_CANE_TYPE, M_CANE_QUAL, M_SH_SUP_Wt, M_EXC_SUP_Wt, M_PAY_LOCK, M_Pay_Lock_Dt, M_Pay_Lock_opr, M_TOK_NO, M_TOK_DT, M_TOK_TM, M_TOK_OPR, M_TOK_SHIFT, M_TOK_COUNTERNO, M_TOK_KANTANO, M_TOK_IMAGE, M_GROS_DT, M_GROS_OPR, M_GROS_SHIFT, M_GROS_COUNTERNO, M_GROS_FIMAGE, M_GROS_BIMAGE, M_CardNo, M_TARE_DT, M_TARE_OPR, M_SHIFT, M_IND_NO, M_IND_DT, M_IND_CN, M_W_CLERK, M_ADV_AMT, M_ADV_DT, M_SYS_DT, M_SYS_TM, M_CASHIER, M_SCROLL, m_adnl_amt, Token_no, M_REMARK, M_HHT, M_CENTRE_HHT, m_adv_Amt20, M_REMARK2, M_G_UNIQUE_NO, M_BILLA_NO, m_hologram_no, m_adv_paid, M_FImagName, M_BImagName, M_PAYABLE, M_BD_CD, M_PurDate, ID,M_reprint, M_cr_by, m_cr_date,       'OLD', GETDATE(),   'UPDATE',CONVERT(NVARCHAR,ISNULL(CONNECTIONPROPERTY('client_net_address'),'')),CONVERT(NVARCHAR,ISNULL(SUSER_SNAME(),'')),CONVERT(NVARCHAR,ISNULL(HOST_NAME(),'')) from  deleted D ;    
end    

If exists (Select * from inserted) and not exists(Select * from deleted)    
begin    
    INSERT into LOG_MILLPUR(M_NUMBER, M_DATE, M_CENTRE, M_Alt_Cen, M_SOC, M_MODE, M_VILL, M_GROW, M_CATEG, M_VAR, M_GROSS, M_TARE, M_JOONA, M_FinalWt, M_AMOUNT, M_SMP_AMT, M_ADDI_AMT, M_CounterNo, M_GrossSlipNo, M_BillNo, M_HologramNo, M_TROLLYNO, M_CANE_TYPE, M_CANE_QUAL, M_SH_SUP_Wt, M_EXC_SUP_Wt, M_PAY_LOCK, M_Pay_Lock_Dt, M_Pay_Lock_opr, M_TOK_NO, M_TOK_DT, M_TOK_TM, M_TOK_OPR, M_TOK_SHIFT, M_TOK_COUNTERNO, M_TOK_KANTANO, M_TOK_IMAGE, M_GROS_DT, M_GROS_OPR, M_GROS_SHIFT, M_GROS_COUNTERNO, M_GROS_FIMAGE, M_GROS_BIMAGE, M_CardNo, M_TARE_DT, M_TARE_OPR, M_SHIFT, M_IND_NO, M_IND_DT, M_IND_CN, M_W_CLERK, M_ADV_AMT, M_ADV_DT, M_SYS_DT, M_SYS_TM, M_CASHIER, M_SCROLL, m_adnl_amt, Token_no, M_REMARK, M_HHT, M_CENTRE_HHT, m_adv_Amt20, M_REMARK2, M_G_UNIQUE_NO, M_BILLA_NO, m_hologram_no, m_adv_paid, M_FImagName, M_BImagName, M_PAYABLE, M_BD_CD, M_PurDate, ID,M_reprint, M_cr_by, m_cr_date,  LOG_STATUS, LOG_DATE,   LOG_ACTIVITY,          LOG_IP,                                                                      LOG_SQLUSER,                  LOG_PCNAME)                           
                     Select M_NUMBER, M_DATE, M_CENTRE, M_Alt_Cen, M_SOC, M_MODE, M_VILL, M_GROW, M_CATEG, M_VAR, M_GROSS, M_TARE, M_JOONA, M_FinalWt, M_AMOUNT, M_SMP_AMT, M_ADDI_AMT, M_CounterNo, M_GrossSlipNo, M_BillNo, M_HologramNo, M_TROLLYNO, M_CANE_TYPE, M_CANE_QUAL, M_SH_SUP_Wt, M_EXC_SUP_Wt, M_PAY_LOCK, M_Pay_Lock_Dt, M_Pay_Lock_opr, M_TOK_NO, M_TOK_DT, M_TOK_TM, M_TOK_OPR, M_TOK_SHIFT, M_TOK_COUNTERNO, M_TOK_KANTANO, M_TOK_IMAGE, M_GROS_DT, M_GROS_OPR, M_GROS_SHIFT, M_GROS_COUNTERNO, M_GROS_FIMAGE, M_GROS_BIMAGE, M_CardNo, M_TARE_DT, M_TARE_OPR, M_SHIFT, M_IND_NO, M_IND_DT, M_IND_CN, M_W_CLERK, M_ADV_AMT, M_ADV_DT, M_SYS_DT, M_SYS_TM, M_CASHIER, M_SCROLL, m_adnl_amt, Token_no, M_REMARK, M_HHT, M_CENTRE_HHT, m_adv_Amt20, M_REMARK2, M_G_UNIQUE_NO, M_BILLA_NO, m_hologram_no, m_adv_paid, M_FImagName, M_BImagName, M_PAYABLE, M_BD_CD, M_PurDate, ID,M_reprint, M_cr_by, m_cr_date,       'NEW', GETDATE(),   'INSERT',CONVERT(NVARCHAR,ISNULL(CONNECTIONPROPERTY('client_net_address'),'')),CONVERT(NVARCHAR,ISNULL(SUSER_SNAME(),'')),CONVERT(NVARCHAR,ISNULL(HOST_NAME(),'')) from  inserted i ;    
end    

If exists(select * from deleted) and not exists(Select * from inserted)    
begin    
        INSERT into LOG_MILLPUR(M_NUMBER, M_DATE, M_CENTRE, M_Alt_Cen, M_SOC, M_MODE, M_VILL, M_GROW, M_CATEG, M_VAR, M_GROSS, M_TARE, M_JOONA, M_FinalWt, M_AMOUNT, M_SMP_AMT, M_ADDI_AMT, M_CounterNo, M_GrossSlipNo, M_BillNo, M_HologramNo, M_TROLLYNO, M_CANE_TYPE, M_CANE_QUAL, M_SH_SUP_Wt, M_EXC_SUP_Wt, M_PAY_LOCK, M_Pay_Lock_Dt, M_Pay_Lock_opr, M_TOK_NO, M_TOK_DT, M_TOK_TM, M_TOK_OPR, M_TOK_SHIFT, M_TOK_COUNTERNO, M_TOK_KANTANO, M_TOK_IMAGE, M_GROS_DT, M_GROS_OPR, M_GROS_SHIFT, M_GROS_COUNTERNO, M_GROS_FIMAGE, M_GROS_BIMAGE, M_CardNo, M_TARE_DT, M_TARE_OPR, M_SHIFT, M_IND_NO, M_IND_DT, M_IND_CN, M_W_CLERK, M_ADV_AMT, M_ADV_DT, M_SYS_DT, M_SYS_TM, M_CASHIER, M_SCROLL, m_adnl_amt, Token_no, M_REMARK, M_HHT, M_CENTRE_HHT, m_adv_Amt20, M_REMARK2, M_G_UNIQUE_NO, M_BILLA_NO, m_hologram_no, m_adv_paid, M_FImagName, M_BImagName, M_PAYABLE, M_BD_CD, M_PurDate, ID,M_reprint, M_cr_by, m_cr_date,  LOG_STATUS, LOG_DATE,  LOG_ACTIVITY,               LOG_IP,                   LOG_SQLUSER,                LOG_PCNAME)                       
                     Select     M_NUMBER, M_DATE, M_CENTRE, M_Alt_Cen, M_SOC, M_MODE, M_VILL, M_GROW, M_CATEG, M_VAR, M_GROSS, M_TARE, M_JOONA, M_FinalWt, M_AMOUNT, M_SMP_AMT, M_ADDI_AMT, M_CounterNo, M_GrossSlipNo, M_BillNo, M_HologramNo, M_TROLLYNO, M_CANE_TYPE, M_CANE_QUAL, M_SH_SUP_Wt, M_EXC_SUP_Wt, M_PAY_LOCK, M_Pay_Lock_Dt, M_Pay_Lock_opr, M_TOK_NO, M_TOK_DT, M_TOK_TM, M_TOK_OPR, M_TOK_SHIFT, M_TOK_COUNTERNO, M_TOK_KANTANO, M_TOK_IMAGE, M_GROS_DT, M_GROS_OPR, M_GROS_SHIFT, M_GROS_COUNTERNO, M_GROS_FIMAGE, M_GROS_BIMAGE, M_CardNo, M_TARE_DT, M_TARE_OPR, M_SHIFT, M_IND_NO, M_IND_DT, M_IND_CN, M_W_CLERK, M_ADV_AMT, M_ADV_DT, M_SYS_DT, M_SYS_TM, M_CASHIER, M_SCROLL, m_adnl_amt, Token_no, M_REMARK, M_HHT, M_CENTRE_HHT, m_adv_Amt20, M_REMARK2, M_G_UNIQUE_NO, M_BILLA_NO, m_hologram_no, m_adv_paid, M_FImagName, M_BImagName, M_PAYABLE, M_BD_CD, M_PurDate, ID,M_reprint, M_cr_by, m_cr_date,       'OLD',    GETDATE(),   'DELETE',CONVERT(NVARCHAR,ISNULL(CONNECTIONPROPERTY('client_net_address'),''))LOG_IP,CONVERT(NVARCHAR,ISNULL(SUSER_SNAME(),''))LOG_SQLUSER,CONVERT(NVARCHAR,ISNULL(HOST_NAME(),''))LOG_PCNAME from  deleted D ;    
        end

write your query in this way by only changing the columns name of the insert and select command of this code

0
votes

Insert Trigger:

create or replace trigger table_a_insert before insert on table_a for each row
begin
  insert into table_a_audit
    (audit_timestamp, audit_action, key_value, value)
  values
    (systimestamp, 'I', :new.key_value, :new.value);
end;

Update Trigger:

create or replace trigger table_a_update before update on table_a for each row
begin
  UPDATE table_a 
set value=:new.value
WHERE table_a.key_value= :new.key_value;   
end;

It was perfectly worked on Oracle 9i and 12C version. (insert and update triggers). IN case of any queries, please let us know.