- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Oracle 9i sqlplus Trigger
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-26-2005 05:28 PM
тАО05-26-2005 05:28 PM
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-26-2005 06:12 PM
тАО05-26-2005 06:12 PM
Re: Oracle 9i sqlplus Trigger
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-26-2005 08:08 PM
тАО05-26-2005 08:08 PM
Re: Oracle 9i sqlplus Trigger
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-26-2005 08:28 PM
тАО05-26-2005 08:28 PM
SolutionHere 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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-27-2005 12:11 AM
тАО05-27-2005 12:11 AM
Re: Oracle 9i sqlplus Trigger
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..!!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-27-2005 02:35 AM
тАО05-27-2005 02:35 AM
Re: Oracle 9i sqlplus Trigger
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-27-2005 02:48 AM
тАО05-27-2005 02:48 AM
Re: Oracle 9i sqlplus Trigger
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.
begin
if :new.tab1_column != :old.tab1_column then
update tab2 set
where
select
into w_tab3_column
from tab3
where
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-27-2005 04:59 AM
тАО05-27-2005 04:59 AM
Re: Oracle 9i sqlplus Trigger
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!!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-29-2005 02:04 PM
тАО05-29-2005 02:04 PM
Re: Oracle 9i sqlplus Trigger
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