Here is my PL/SQL:
Code: Select all
create or replace trigger MACL.ZM_SOCOMM_trigger
after delete or insert or update on MACL.SOCOMM
for each row
declare
alertData varchar2(1000);
begin
alertData := '{"Schema":"MACL","Table":"SOCOMM","Index":"I_SOCOM_KEY","Columns":[{"COLUMN_NAME":"SOCOM_ORDREF","COLUMN_POSITION":1,"COLUMN_VALUE":"#SOCOM_ORDREF#"},{"COLUMN_NAME":"SOCOM_ORDLINE","COLUMN_POSITION":2,"COLUMN_VALUE":"#SOCOM_ORDLINE#"},{"COLUMN_NAME":"SOCOM_COMSEQ","COLUMN_POSITION":3,"COLUMN_VALUE":"#SOCOM_COMSEQ#"},{"COLUMN_NAME":"SOCOM_COMTYPE","COLUMN_POSITION":4,"COLUMN_VALUE":"#SOCOM_COMTYPE#"},{"COLUMN_NAME":"SOCOM_TEXT","COLUMN_POSITION":5,"COLUMN_VALUE":"#SOCOM_TEXT#"}],"ChangePlaceHolder":"#CHANGE#"}';
IF INSERTING OR UPDATING THEN
alertData := replace(alertData, '#SOCOM_ORDREF#', :new.SOCOM_ORDREF);
alertData := replace(alertData, '#SOCOM_ORDLINE#', :new.SOCOM_ORDLINE);
alertData := replace(alertData, '#SOCOM_COMSEQ#', :new.SOCOM_COMSEQ);
alertData := replace(alertData, '#SOCOM_COMTYPE#', :new.SOCOM_COMTYPE);
alertData := replace(alertData, '#SOCOM_TEXT#', :new.SOCOM_TEXT);
ELSIF DELETING THEN
alertData := replace(alertData, '#SOCOM_ORDREF#', :old.SOCOM_ORDREF);
alertData := replace(alertData, '#SOCOM_ORDLINE#', :old.SOCOM_ORDLINE);
alertData := replace(alertData, '#SOCOM_COMSEQ#', :old.SOCOM_COMSEQ);
alertData := replace(alertData, '#SOCOM_COMTYPE#', :old.SOCOM_COMTYPE);
alertData := replace(alertData, '#SOCOM_TEXT#', :old.SOCOM_TEXT);
END IF;
IF INSERTING THEN
alertData := replace(alertData, '#CHANGE#', 'INSERT');
ELSIF DELETING THEN
alertData := replace(alertData, '#CHANGE#', 'DELETE');
ELSE
alertData := replace(alertData, '#CHANGE#', 'UPDATE');
END IF;
sys.dbms_alert.signal('ooalert_sync', alertData);
END;
Code: Select all
public void SetUpAlerts() => RegisterHandlers(CreateAlerter());
private static void RegisterHandlers(OracleAlerter alerter)
{
alerter.Alert += AlerterOnAlert;
alerter.Error += AlerterOnError;
alerter.WaitTimeout += AlerterOnWaitTimeout;
alerter.Stopped += AlerterOnStopped;
alerts.Add(alerter);
alerter.Start();
}
private OracleAlerter CreateAlerter() => new OracleAlerter
{
Timeout = Day,
AlertName = "Name",
Connection = Factory.CreateSourceConnection() as OracleConnection
};
private static void AlerterOnAlert(object sender, OracleAlerterAlertEventArgs args)
{
//handle alert
}
https://community.oracle.com/thread/3928329
http://stackoverflow.com/questions/3713 ... le-actions
Is there something that I am doing wrong here?