topic Re: Oracle 9i sqlplus Trigger in Operating System - HP-UX

Oracle 9i sqlplus Trigger

Henry Chua — Fri, 27 May 2005 00:28:29 GMT

Hi guys,

I need to create a trigger using sqlplus. Anyone know how I can do that?

I want to create a trigger after I added newline is added in a table. then using the data from the new line to write to another table. How should I go about doin it?

Thank u

Henry

Re: Oracle 9i sqlplus Trigger

renarios — Fri, 27 May 2005 01:12:38 GMT

Hi Henry,

I suppose a better site for this is otn.oracle.com.
Before creating a trigger, decide what kind of a trigger it has to be.
Here's the syntax:
CREATE [OR REPLACE] TRIGGER [schema .] trigger
{ BEFORE | AFTER | INSTEAD OF }
{ dml_event_clause
| { ddl_event [OR ddl_event]...
| database_event [OR database_event]...
}
ON { [schema .] SCHEMA | DATABASE }
}
[WHEN ( condition ) ]
{ pl/sql_block | call_procedure_statement }

I 'll also attach the sql reference as gif.

Have fun!

Cheerio,

Renarios

Re: Oracle 9i sqlplus Trigger

Eric Antunes — Fri, 27 May 2005 03:08:21 GMT

Hi Henry,

Here an example of one trigger I created here at my site:

CREATE OR REPLACE TRIGGER APPS.CUST_AR_RECEIVABLE_APPLIC_BRIU
before insert on AR.AR_RECEIVABLE_APPLICATIONS_ALL
for each row
BEGIN
if :new.application_type != 'CM' then
if nvl( :new.display, :old.display) = 'Y' and nvl( :new.status, :old.status) in ('ACC', 'APP') then
if to_char( nvl( :new.apply_date, :old.apply_date) , 'MON-RRRR') !=
to_char( nvl( :new.gl_date, :old.gl_date), 'MON-RRRR') or
to_char( nvl( :new.apply_date, :old.apply_date), 'DD-MM-RRRR') = to_char( sysdate, 'DD-MM-RRRR')
then
RAISE_APPLICATION_ERROR( -20999, 'Invalid date for application!');
end if;
elsif to_char( nvl( :new.reversal_gl_date, :old.reversal_gl_date), 'DD-MM-RRRR') =
to_char( sysdate, 'DD-MM-RRRR') then
RAISE_APPLICATION_ERROR( -20998, 'Invalid date for reverse!');
end if;
end if;
END;

Best Regards,

Eric Antunes

Re: Oracle 9i sqlplus Trigger

Indira Aramandla — Fri, 27 May 2005 03:28:19 GMT

Hi Henry,

Here is a simple example of how you do it.

Create two tables tab1 and tab2

Create tab1 (eno number(10));

Create tab2 (enum number(10));

Now create a trigger to insert into tab2 each time a row is inserted into tab1 like this:-

create or replace trigger ins_tab2 AFTER INSERT ON tab1
REFERENCING NEW AS N OLD AS O FOR EACH ROW
begin
insert into tab2l (enum) values (:N.ENO);
end;

trigger created.

Now insert a record into tab1.

SQL> insert into tab1 (eno) valies (101);
1 row created.

SQL> select * from tab1;

ENO
----------
101

SQL> select * from tab2;

ENUM
----------
101
Here the trigger inserted into tab2 when you inserted into tab1

Indira A

Re: Oracle 9i sqlplus Trigger

Henry Chua — Fri, 27 May 2005 07:11:12 GMT

Hi.. thanks guys for the inputs...

Just somemore query,

IF i want the trigger to update another table (table2) instead of inserting a new value when it gotten a new input from one table (table1). And display data from (table3) when the update is complete.. how can this be done..

Thanks in advance..!!

Re: Oracle 9i sqlplus Trigger

John Wimmer_1 — Fri, 27 May 2005 09:35:46 GMT

Display where? If the updating is done using sqlplus, then you can use the dbms_output package. If it's going through a tool (forms or a custom tool) you may be better off not using a trigger and coding it into the tool.

Re: Oracle 9i sqlplus Trigger

Eric Antunes — Fri, 27 May 2005 09:48:39 GMT

Hi again,

I think this is what you want:

create or replace trigger updt_tab2 after insert on tab1
for each row
declare

w_tab3_column tab3.%type;

begin

if :new.tab1_column != :old.tab1_column then

update tab2 set =<:new.tab1_column>
where =<:new.tab1_column_id>;

select
into w_tab3_column
from tab3
where =<:new.tab3_column_id>;

raise_application_error( -20999, 'Your message'); --if you don't want it to be a error message use dbms_output package like John said...
end if;
end;

Regards,

Eric Antunes

Re: Oracle 9i sqlplus Trigger

Henry Chua — Fri, 27 May 2005 11:59:35 GMT

Many thanks Eric...

with your help i did this..

create or replace trigger data_trigger
after insert on tab1
for each row
declare
new_data tab2.data%type;
begin
update tab2
set tab2_data = tab2_data -:tab1_data
where tab1_id = :new.tab2_id;
IF ( new_data < 100) THEN
DBMS_OUTPUT.PUT_LINE ( 'amount = ' || new_data );
END IF;
END;
/

The trigger works logically subtracting the amount from the total when triggered.. but it wont display the amount when I used the dms_output.put_line function.. if I want the system to display the amount left how should i do it?

thanks!!

Re: Oracle 9i sqlplus Trigger

Indira Aramandla — Sun, 29 May 2005 21:04:45 GMT

Hi Henry,

You will require to set serveroutput on

If serveroutput is not turned on, nothing will be displayed even if you call the DBMS_OUTPUT.ENABLE packaged procedure.

In a PL/SQL program, calls to DBMS_OUTPUT.PUT_LINE will add lines to the buffer.
Nothing is actually displayed until the program executes completely and control is transfered back to SQL*Plus or SQL*DBA. At this point, if serveroutput is on, SQL*Plus or SQL*DBA will make a call to DBMS_OUTPUT.GET_LINES and print the results to the screen.

Indira A