Creating a trigger

Hi all,
I've never played with triggers before but I need to create one, so I guess
I need some help!!
I've got a table TABLE1 in DATABASE1 and it has 2 fields MYID and MYNAME.
This is where the trigger needs to be created. MYID refers to a field in
another table in another database.
I need a trigger to update (on insert only) MYNAME according to what MYID is
by looking into the other table and retrieving the value.
Should be easy for the gurus in here!!
Any ideas?
Thanks,
IvanUntested:
USE Database1
GO
CREATE TRIGGER trg_table1 ON Table1
FOR INSERT
AS
UPDATE Table1
SET myname =
(SELECT myname
FROM database2.dbo.table1 AS T
WHERE myid = Table1.myid)
WHERE EXISTS
(SELECT *
FROM Inserted
WHERE myid = Table1.myid)
Perhaps a better option though would be to create a view instead:
CREATE VIEW table1_with_name
(myid,myname)
AS
SELECT T1.myid, COALESCE(T1.myname,T2.myname)
FROM database1.dbo.Table1 AS T1
LEFT JOIN database2.dbo.Table1 AS T2
ON T1.myid = T2.myid
Either way, it can be useful to indirect all external database
references through views because it reduces the number of things that
need changing if you decide to relocate a database.
David Portas
SQL Server MVP
--|||Try,
create trigger dbo.tr_table1 on dbo.table1
for insert
as
set nocount on
update dbo.table1
set myname = (select a.myname from database2..tablex as a where a.myid =
table1.myid)
where exists(select * from inserted as i where i.myid = table1.myid)
AMB
"Ivan Debono" wrote:

> Hi all,
> I've never played with triggers before but I need to create one, so I gues
s
> I need some help!!
> I've got a table TABLE1 in DATABASE1 and it has 2 fields MYID and MYNAME.
> This is where the trigger needs to be created. MYID refers to a field in
> another table in another database.
> I need a trigger to update (on insert only) MYNAME according to what MYID
is
> by looking into the other table and retrieving the value.
> Should be easy for the gurus in here!!
> Any ideas?
> Thanks,
> Ivan
>
>|||Thanks :)
Ivan
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> schrieb im
Newsbeitrag news:1114524112.314396.275370@.g14g2000cwa.googlegroups.com...
> Untested:
> USE Database1
> GO
> CREATE TRIGGER trg_table1 ON Table1
> FOR INSERT
> AS
> UPDATE Table1
> SET myname =
> (SELECT myname
> FROM database2.dbo.table1 AS T
> WHERE myid = Table1.myid)
> WHERE EXISTS
> (SELECT *
> FROM Inserted
> WHERE myid = Table1.myid)
> Perhaps a better option though would be to create a view instead:
> CREATE VIEW table1_with_name
> (myid,myname)
> AS
> SELECT T1.myid, COALESCE(T1.myname,T2.myname)
> FROM database1.dbo.Table1 AS T1
> LEFT JOIN database2.dbo.Table1 AS T2
> ON T1.myid = T2.myid
> Either way, it can be useful to indirect all external database
> references through views because it reduces the number of things that
> need changing if you decide to relocate a database.
> --
> David Portas
> SQL Server MVP
> --
>

creating a text file from the contents of the database?

please help!!

i'm working on a project right now using Oracle Forms 6.0 and Oracle9i.

after i create a record and save the data in the table, how can i generate/create a text file of that particular record? i need this text file in order to run it in another computer and upload the data in the text file to another database (also Oracle).

i will also need to create the text file for multiple records.

can someone help me please?? i read something about SELECT INTO OUTFILE... how exactly does this work?Hello,

use UTL_FILE package to spool records into a file via PL/SQL.
In AlligatorSQL you can use a template "How to spool a ...".

See at http://www.alligatorsql.com/download/alligator116.zip

But if you wish I can post an example again (it has been already posted in this forum)

Hope that helps ?

Manfred Peter
(Alligator Compay Software GmbH)
http://www.alligatorsql.com|||oh i see! thanks, i found the thread on extracting. will post again if i have any problems!|||sir manfred,

would it be possible to use TEXT_IO instead of UTL_FILE? Oracle Forms does not have the UTL_FILE package. i had a bit of difficulty following your examples (sorry!) as i am just a beginner with pl/sql.

this is what i have to do:
- save the information that was entered in Oracle Forms (this is finished)
- when a button is pressed, update the REQUEST_SENT flag and create the text file (of that same form which was just saved)

This is what i have done so far:

/*WHEN-BUTTON-PRESSED trigger*/

DECLARE

CURSOR cuProcess IS
SELECT *
FROM SIR
WHERE SIR_TRANS_NO = :SIR.SIR_TRANS_NO and SIR_COMPANY = :SIR.SIR_COMPANY;

rProcess cuProcess%ROWTYPE;
cOut VARCHAR2(2000);

N_FILE VARCHAR2(2000);

BEGIN

UPDATE SIR
SET SIR_REQUEST_SENT = 'Y'
WHERE SIR_TRANS_NO = :SIR.SIR_TRANS_NO AND SIR_COMPANY = :SIR.SIR_COMPANY;
COMMIT;

OPEN cuProcess;
FETCH cuProcess INTO rProcess;

WHILE cuProcess%FOUND LOOP
FETCH cuProcess INTO rProcess;

cOut := rProcess.SIR_TRANS_NO || ';'
|| rProcess.SIR_COMPANY || ';'
|| rProcess.SIR_PROJECT || ';'
|| rProcess.SIR_APPL || ';'
|| rProcess.SIR_BUS_FUN || ';'
|| rProcess.SIR_REPORTED_BY || ';'
|| rProcess.SIR_HANDLED_BY || ';'
|| rProcess.SIR_PHASE || ';'
|| rProcess.SIR_TYPE || ';'
|| rProcess.SIR_CAUSE || ';'
|| rProcess.SIR_CLASSIFICATION || ';'
|| rProcess.SIR_DESCRIPTION || ';'
|| rProcess.SIR_REASON || ';'
|| rProcess.SIR_REMARKS || ';'
|| rProcess.SIR_STATUS || ';'
|| rProcess.SIR_REQUEST_DATE || ';'
|| rProcess.SIR_RECEIVED_DATE || ';'
|| rProcess.SIR_START_DATE || ';'
|| rProcess.SIR_CLOSE_DATE || ';'
|| rProcess.SIR_TARGET_DATE || ';'
|| rProcess.SIR_ESTIMATED_MANHRS || ';'
|| rProcess.SIR_ACTUAL_MANHRS || ';'
|| rProcess.SIR_BILLABLE_MANHRS || ';'
||rProcess.SIR_ATTACHMENT || ';'
|| rProcess.SIR_REQUEST_SENT;
END LOOP BeginLoop;

CLOSE cuProcess;

CREATE_TEXT('filename', cOut);

EXCEPTION
WHEN OTHERS THEN
IF cuProcess%ISOPEN THEN
CLOSE cuProcess;
END IF;

END;

then i have a simple procedure that creates the text file:

PROCEDURE CREATE_TEXT (pfilename IN VARCHAR2, selected IN VARCHAR2) IS

N_FILE text_io.file_type;

BEGIN
N_FILE := TEXT_IO.FOPEN(pfilename||'.TXT', 'W');
TEXT_IO.PUT_LINE(N_FILE, selected);
TEXT_IO.FCLOSE(N_FILE);

END;

my problem is that i have to press the button twice for the update to happen. is there another way that i can first update SIR_REQUEST_SENT and then use a cursor to SELECT * ?
also, after the text file is created, how can i load it using sqlloader?

also, how can i specify the path where the text file will be saved? the TEXT_IO.FOPEN accepts only 2 parameters, the filename and the mode unlike UTL_FILE.FOPEN

i appreciate the help! thanks again!|||Hello,

sorry, but I am not so familiar with Oracle forms. But I know, that you can call PL/SQL routines from Forms.

Sorry again.

Manfred Peter
(Alligator Company Software GmbH)
http://www.alligatorsql.com