Home » RDBMS Server » Server Utilities » Trigger problem
Trigger problem [message #72695] |
Fri, 19 September 2003 05:15 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
kjl
Messages: 48 Registered: June 2003
|
Member |
|
|
Hi,
I need a trigger which runs when a new inventory_part has made.
Triggen should make a CSV-file in a specified place. CSV-file
should look like:
INSERT INTO INVENTORY_PART_TAB ( PART_NO, PLACE, QTY, ROWVERSION ) VALUES (
'AAA100', B12, TO_Date( '19/09/2003 11:21:16 AP.', 'MM/DD/YYYY HH:MI:SS AM'));
How can I do that kind of trigger?
BR K
|
|
|
Re: Trigger problem [message #72696 is a reply to message #72695] |
Fri, 19 September 2003 16:31 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/42800.jpg) |
Mahesh Rajendran
Messages: 10707 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
first
you have set ur utl direcotory.
in 8i, set utl_file_dir in init.ora
in 9i, do this method.
-- login as sys and create the directory.
sys@mutation_mutation > create directory rep_dir as '/home/oracle/scr/ora/rep';
Directory created.
sys@mutation_mutation > grant read on directory rep_dir to mag;
Grant succeeded.
sys@mutation_mutation > grant write on directory rep_dir to mag;
Grant succeeded.
----------------------------------------------------------------------
lets test whether the direcotry works.
now your pl/sql programs can create files in the created direcotry.
your pl/sql programs should make use of builtin UTL_FILE.
-- i have written a very simple utility write_log
-- that will make use of pl/sql built in utl_file.
-- and will save lotsa code
-- the write_log procedure takes three arguments
-- name of file to be created,message and mode
mag@mutation_mutation > @write_log
Procedure created.
mag@mutation_mutation > begin
2 write_log('test_utl.txt','this is just samople text.','A');
3 end;
4 /
PL/SQL procedure successfully completed.
mag@mutation_mutation > !cat rep/test_utl.txt
this is just samople text.
----------------------------------------------------------------------
now lets create the trigger.
-- this trigger fires for evey inserted row
-- and writes the contents into a OS file.
-- I am passing the name of the file as input
-- note that, i am opening the file script.csv in
-- append mode. so that every time, the records/lines
-- are appended.
-- You have decide an logic to clean the files daily
-- or whenever required.
-- you can just 'touch' them
mag@mutation_mutation > get tr
1 create or replace trigger csv_dept
2 after insert on dept
3 for each row
4 begin
5 write_log('script.csv','insert into sometable ('''||:new.deptno||''','''||:new.loc||''','''||:new.dname||''','''||sysdate||''');','A');
6* END;
mag@mutation_mutation > @tr
Trigger created.
mag@mutation_mutation > insert into dept values ('23','mydept','mystreet');
1 row created.
mag@mutation_mutation > /
1 row created.
mag@mutation_mutation > insert into dept values ('25','asdf','asdf');
1 row created.
mag@mutation_mutation > !pwd
/home/oracle/scr/ora
mag@mutation_mutation > !cat rep/script.csv
insert into sometable ('23','mystreet','mydept','09/19/03');
insert into sometable ('23','mystreet','mydept','09/19/03');
insert into sometable ('25','asdf','asdf','09/19/03');
----------------------------------------------------------------------
-- this is source code for write_log
-- you have to only one change
-- in line 18
-- 18 log_file := UTL_FILE.FOPEN('REP_DIR',fname,mo);
-- i have used this REP_DIR for our example.
-- replace with your directory name created in step1.
-- you can have mutltiple directories say REP_DIR2.
-- create a new packge WRITE_LOG2 and use it.
-- You can give any filename you want, to be crated.
mag@mutation_mutation > get write_log
1 -- Enterprise Services: Mahesh Rajendran
2 -- notes:
3 -- 1. Stored Procedure to write the log files for the Lawson_etl process
4 -- 2. Takees 3 input parameters -> filename, message to be written and Mode (read or write)
5 -- in which the log file is opened.
6 -- Output -> written to OS file.
7 -- 3. init parameter utl_file_dir = /rman/logs and file permsisions are given.
8 -- 4. The Most common exceptions are handled, will add any more.
9 CREATE OR REPLACE PROCEDURE write_log
10 (
11 fname IN VARCHAR2 ,
12 message IN VARCHAR2 ,
13 mo IN VARCHAR2
14 )
15 IS
16 log_file UTL_FILE.FILE_TYPE; -- file handle for the log file
17 BEGIN
18 log_file := UTL_FILE.FOPEN('REP_DIR',fname,mo);
19 UTL_FILE.PUTF(log_file,'%sn',message);
20 UTL_FILE.FCLOSE(log_file);
21 EXCEPTION
22 WHEN NO_DATA_FOUND THEN
23 DBMS_OUTPUT.PUT_LINE('no_data_found');
24 WHEN UTL_FILE.INVALID_PATH THEN
25 DBMS_OUTPUT.PUT_LINE('UTL_FILE.INVALID_PATH');
26 WHEN UTL_FILE.WRITE_ERROR THEN
27 DBMS_OUTPUT.PUT_LINE('UTL_FILE.WRITE_ERROR');
28 WHEN OTHERS THEN
29 DBMS_OUTPUT.PUT_LINE('other stuff');
30* END;
mag@mutation_mutation >
|
|
|
Goto Forum:
Current Time: Fri Sep 06 09:42:47 CDT 2024
|