oracle trigger the use of

2010-08-29  来源:本站原创  分类:Database  人气:169 

1. Objective:
Network Office reception Order found netb_order only the contents of a leapfrog because of complaints received, the user need to see the pre-acceptance list on No. 10000 return customer personnel on orders, as orders for different types according to the order form, associated with different list of orders, customer service response number 10000 there are different schedules in order, so consider using the trigger at the database level. This small amount of change

2. To achieve:

a. First of all, adding a field in netb_order replycontent called customer service number is used to keep the order of 10,000 responses
b. In the list of all the various orders with trigger, when a customer service number 10000, when added back, trigger the content will automatically sync back to the list of replycontent field netb_order

What is the trigger?

Trigger
Is there a particular event when the automatic execution of code blocks. Similar to the stored procedure, but the user can not directly call them.
Function:
1, to allow / restrict changes to the table 2, automatically generate derived column, such as increasing the field from 3, 4 mandatory data consistency, to provide auditing and logging, 5, 6, to prevent invalid transactions, enabling complex business logic

Write an example of the first play, set up two tables test1, test2, table structure, like when to insert data into table test1 when using the trigger, automatically insert into test2 also a data table

create or replace trigger tr_insert_test1
  before insert on test1
  for each row
begin

insert into test2 values(:new.id,:new.name);

end;

When using the insert into test1 (id, name) values ('123 ',' huxl '); test2 table when a record is also more

: New - latest column for a reference value;
: Old - a reference to the previous column value; these two variables only use the keyword "FOR EACH ROW" only exist. And the update statement, the two have, and insert only: new, delect only: old;

before that triggered the operation is complete, after that the trigger after the completion of

Following the completion of our business

3. Steps  :
a.  See historical data will sync to netb_order table  

update netb_order o  set o.replycontent= (select up.replycontent
from
netb_PackagesNewSetUp up  where up.id=o.businessid);

update netb_order o  set o.replycontent= (select ps.reply
from
netb_phonenewsetup ps  where ps.id=o.businessid);

update netb_order o  set o.replycontent= (select sr.reply
from
netb_selfhoodreq sr  where sr.selfhoodreqid=o.businessid);

update netb_order o  set o.replycontent= (select ci.reply
from
netb_cards_info ci  where ci.id=o.businessid);

update netb_order o  set o.replycontent= (select bo.reply
from
netb_basicorder bo  where bo.id=o.businessid);

2.  To order from the table and add a trigger  

drop trigger xxx

 Triggers the need for a single execution  

create or replace trigger tr_netb_PackagesNewSetUp
  after update of replycontent on netb_PackagesNewSetUp
  for each row
begin
update netb_order set replycontent=:new.replycontent where businessid=:old.id;

end;

create or replace trigger tr_netb_phonenewsetup
  after update of reply on netb_phonenewsetup
  for each row
begin
update netb_order set replycontent=:new.reply where businessid=:old.id;

end;

create or replace trigger tr_netb_selfhoodreq
  after update of reply on netb_selfhoodreq
  for each row
begin
update netb_order set replycontent=:new.reply where businessid=:old.selfhoodreqid;

end;

create or replace trigger tr_netb_cards_info
  after update of reply on netb_cards_info
  for each row
begin
update netb_order set replycontent=:new.reply where businessid=:old.id;

end;

create or replace trigger tr_netb_basicorder
  after update of reply on netb_basicorder
  for each row
begin
update netb_order set replycontent=:new.reply where businessid=:old.id;

end;

Reference:

http://hi.baidu.com/zhangyadong110/blog/item/289220233f983baf4723e807.html

http://space.itpub.net/12045182/viewspace-613908

相关文章
  • Oracle Trigger Practice 2010-03-19

    --[6]// Oracle Trigger ---------------------------------------------------------------------------------------------// -- Instance 1------------------------ -- Create trigger, when a user on test Table when you perform DML statements , Copies the rel

  • oracle trigger Study Notes _ 2010-03-28

    oracle trigger Study Notes _ Trigger Is there a particular event when the automatic execution of code block. Similar to the stored procedure, but the user can not directly call them. Function: 1, to allow / restrict changes to the table 2, automatica

  • Switch to Oracle Trigger study 2010-06-22

    A practical code of oracle trigger sql code - [6] / / Oracle Trigger -------------------------------------------------- -------------------------------------------// - Example 1 ------------------------ - Create a trigger, when the user of the test t

  • oracle trigger. stored procedure. function. Package 2010-09-09

    1. Oracle trigger the use of create or replace trigger tri_userid before insert on t_user for each row declare new_key number; begin select seq_userid.nextval into new_key from dual; :new.id:=new_key; end; 2. Stored Procedures create or replace proce

  • Oracle trigger syntax and examples 2010-09-26

    An Oracle trigger syntax Trigger is a specific event occurs when the automatic execution of code block. Similar to stored procedures, triggers and stored procedures difference: stored procedure or application by the user explicitly invoked, but trigg

  • Oracle triggers (Oracle Trigger) 2011-02-22

    Recent work in the need to write triggers, temporary Internet search a bit about Oracle trigger information, the basic definition of the following format: Grammar rules: Create [or replace] trigger [mode] trigger name Before | after insert | delete |

  • Detailed oracle trigger 2011-07-07

    oracle trigger study notes _ Trigger Is a specific event occurs when the automatic execution of code blocks. Similar to stored procedures, but users can not directly call them. Function: 1, to allow / restrict changes to the table 2, automatically ge

  • Oracle Trigger insert data with the primary key from the increase 2010-03-01

    1: First of all, you need to have a table! CREATE TABLE example ( ID Number (4) NOT NULL PRIMARY KEY, NAME VARCHAR (25), PHONE VARCHAR (10), ADDRESS VARCHAR (50)); Then you need a custom sequence CREATE SEQUENCE emp_sequence INCREMENT BY 1 - Every ti

  • oracle trigger an application scenario 2010-04-14

    Requirements Background Description: In real projects, such a demand: There are two tables, a Products table productinfo, a product recommendation table recommendproduct, product table stores all product information, product recommendation table stor

  • Oracle trigger Xiangjie 2010-04-20

    1, trigger overview Trigger is a specific event occurs when the automatic execution of code block. Similar to the stored procedure, but the user can not directly call them. Function: 1, to allow / restrict changes to the table 2, automatically genera

  • Oracle Trigger Trigger 2010-06-09

    Memo Quick Check: oracle for update insert delete trigger when: http://mamaoyuan625.javaeye.com/blog/448310 Quote CREATE TABLE t1 ( tid VARCHAR2(20), tname VARCHAR2(20) ) CREATE TABLE logg( n_id VARCHAR2(20), n_name VARCHAR2(10), n_value VARCHAR2(40)

  • Oracle trigger an external program 2010-06-13

    oracle insert a data in a table, submitted, how to make external program received the news? 1, the process: ... declare myexcept exception; inserted_count1 number; inserted_count2 number; ... begin insert into table values (...); inserted_count1: = s

  • Hibernian native increment primary key and sequence oracle trigger 2010-10-12

    Using the hibernate function of the automatic construction of the table, if the primary key is int and is native, is the last statement in the construction of the table surface hibernate, there is one sql statement "Create sequence hibernate_sequence

  • oracle trigger entry 2011-03-14

    DML triggers are three types: 1, insert the trigger; 2, update triggers; 3, delete the trigger; Part of the trigger: the trigger statement, specify the trigger timing, the event, the table name to the type of trigger execution, PL / SQL block or a ca

  • oracle trigger the preparation of 2011-03-23

    Triggers are a special type of stored procedure, trigger is mainly triggered by events to be executed, but stored procedure stored procedure name can be called directly. When a table such as UPDATE, INSERT, DELETE these operations, ORACLE will automa

  • Oracle trigger trigger sequence to achieve the use of the data sequence from the table by 2011-07-08

    The first integrated web development with oracle projects, oracle does not find the mysql auto-increment function, now only take a trigger, the sequence of channels, is the way to organize to share to you, for later use: First: create the oracle in t

  • oracle trigger (triggers) 2011-08-08

    -- Trigger trigger --1.DML Trigger DML statements (DELETE, INSERT, UPDATE) /* For a dml sql, with the possible role of multi-line , May be only one line. Trigger for each triggering statement sql, Trigger is executed only once : Row-level trigger is

  • oracle trigger the new, old meaning 2010-03-10

    General habit of using new instead of create trigger statement in the back ON the table name, but do not know when to use the old, does not know the difference between the two! delete, update before the use of old; insert, update with a new post old,

  • Oracle trigger the use of examples 2010-03-18

    create or replace trigger TG_MSISDN_REGISTER_DET before insert on MSISDN_REGISTER_DET_ for each row begin update SIM_CARD_DETAIL_ set registerd_ = 1 where id_ =: new.card_det_id_; end;

  • Database - Oracle trigger / stored procedure / view, etc. 2010-04-08

    1, Merge statement in the table under the conditions of the implementation of the function to modify or insert data, if the purpose of inserting rows of data exist in the table on the implementation of UPDATE, if it does not exist, the implementation