I want to create a file for every new record inserted into table. I have created a trigger after insert and loaded into an another table.
Is it possibe to generate a file. can you please advice me, thanks
CREATE TABLE tab1 (
CREATE TABLE tab2 (
CREATE TRIGGER trig1
AFTER INSERT ON tab1
REFERENCING NEW AS NewRow
FOR EACH ROW (
INSERT INTO tab2
VALUES (NewRow.a, NewRow.b, NewRow.c););
Just to make sure that I'm understanding you correctly, when you refer to 'write a file' I think you mean to write a new record to a flat file, probably on a staging server or a pc. Is that correct?
If not please can you say:
- where do you want to write this file?
- what type of file is it?
If my assumption is correct then you typically need to do this from the computer where you want the file to be written. Then you have to decide which utility (or program that you write) is going to be used to create the file. Whichever utility/program you are using needs to run some SQL to extract the content of your 'tab2'.
If you were using BTEQ then you could do something like:
.logon... .export report file = file-name; sql goes here .export reset; .if errorcode <> 0 then .quit 8; .quit 0;
You also have to decide what format the data should be written out in, the above example uses 'report' format which is essentially just display data.
Start with that and let's see where you get to.
I have crated bteq to create a txt file for every new record insert, it works