--*****************************************************************************

-- SQL Server Insert Trigger

-- for ADDRESS table

-- will generate xml and save it to the OutFolder

--*****************************************************************************

CREATE TRIGGER trgADDRESS_INSERT ON [ADDRESS]

FOR INSERT

As

DECLARE @sSQL VARCHAR(8000)

DECLARE @sOutFolder VARCHAR(255)

--specify the data exchange folder here

select @sOutFolder='c:\temp\'

select @sSQL = 'echo ^<ADDRESSDOC^>^<ADDRESS^>^<SYSID^>'+convert(varchar,isnull(SYSID,""))+'^</SYSID^>^<ADD1^>'+convert(varchar,isnull(ADD1,""))+'^</ADD1^>^<ADD2^>'+convert(varchar,isnull(ADD2,""))+'^</ADD2^>^<CITY^>'+convert(varchar,isnull(CITY,""))+'^</CITY^>^<STATE^>'+convert(varchar,isnull(STATE,""))+'^</STATE^>^<ZIP^>'+convert(varchar,isnull(ZIP,""))+'^</ZIP^>^<SDATE^>'+convert(varchar,isnull(SDATE,""))+'^</SDATE^>^<EDATE^>'+convert(varchar,isnull(EDATE,""))+'^</EDATE^>^<LASTUPDATE^>'+convert(varchar,isnull(LASTUPDATE,""))+'^</LASTUPDATE^>^<USER^>'+convert(varchar,isnull(USER,""))+'^</USER^>^</ADDRESS^>^</ADDRESSDOC^> >'+ @sOutFolder + 'ADDRESS_'+convert(varchar,SYSID)+'.xml' from inserted

exec master..xp_cmdshell @sSQL

--*****************************************************************************

--End of SQL Server Trigger

--*****************************************************************************

--*****************************************************************************

-- Oracle Insert Trigger

--

--* WARNING *

--Following packages must be installed in SYS schema in order to use this trigger

--DBMS_RANDOM, this package can be found in $ORACLE_HOME\Rdbms\Admin\Catoctk.sql

--UTL_FILE, this package can be found $ORACLE_HOME\Rdbms\Admin\utlfile.sql

--$ORACLE_HOME\ADMIN\ocspoke\pfile\init.ora file has to be updated

--Following line has to be added, and Oracle restarted afterwards

--UTL_FILE_DIR="c:\temp\"

--*****************************************************************************

CREATE OR REPLACE TRIGGER trgADDRESS_INSERT AFTER INSERT ON ADDRESS FOR EACH ROW

DECLARE

iFile_Handle UTL_FILE.FILE_TYPE;

i BINARY_INTEGER;

BEGIN

i := DBMS_RANDOM.RANDOM;

--Update with valid exchange folder

iFile_Handle := UTL_FILE.fopen('c:\temp\', 'ADDRESS'|| to_char(i) || '.xml','w',32000);

UTL_FILE.put(iFile_Handle,'<?xml version=""1.0""?><ADDRESSDOC><ADDRESS>');

UTL_FILE.put(iFile_Handle,'<SYSID>'||:new.SYSID||'</SYSID>');

UTL_FILE.put(iFile_Handle,'<ADD1>'||:new.ADD1||'</ADD1>');

UTL_FILE.put(iFile_Handle,'<ADD2>'||:new.ADD2||'</ADD2>');

UTL_FILE.put(iFile_Handle,'<CITY>'||:new.CITY||'</CITY>');

UTL_FILE.put(iFile_Handle,'<STATE>'||:new.STATE||'</STATE>');

UTL_FILE.put(iFile_Handle,'<ZIP>'||:new.ZIP||'</ZIP>');

UTL_FILE.put(iFile_Handle,'<SDATE>'||to_char(:new.SDATE,'mm/dd/yyyy')||'</SDATE>');

UTL_FILE.put(iFile_Handle,'<EDATE>'||to_char(:new.EDATE,'mm/dd/yyyy')||'</EDATE>');

UTL_FILE.put(iFile_Handle,'<LASTUPDATE>'||to_char(:new.LASTUPDATE,'mm/dd/yyyy')||'</LASTUPDATE>');

UTL_FILE.put(iFile_Handle,'<USER>'||:new.USER||'</USER>');

UTL_FILE.put(iFile_Handle,'</ADDRESS></ADDRESSDOC>');

UTL_FILE.fclose(iFile_Handle);

EXCEPTION

When NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_line('no_data_found');

UTL_FILE.FCLOSE(iFile_Handle);

WHEN UTL_FILE.INVALID_PATH THEN

DBMS_OUTPUT.PUT_line('UTL_FILE.INVALID_PATH');

UTL_FILE.FCLOSE(iFile_Handle);

WHEN UTL_FILE.READ_ERROR THEN

DBMS_OUTPUT.PUT_line('UTL_FILE.READ_ERROR');

UTL_FILE.FCLOSE(iFile_Handle);

WHEN UTL_FILE.WRITE_ERROR THEN

DBMS_OUTPUT.PUT_line('UTL_FILE.WRITE_ERROR');

UTL_FILE.FCLOSE(iFile_Handle);

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_line('UTL_FILE.OTHER_ERROR');

UTL_FILE.FCLOSE(iFile_Handle);

END;

--*****************************************************************************

-- End of Oracle Trigger

--*****************************************************************************