1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

oracle triggers

Discussion in 'SQL PL/SQL' started by ora_student, Nov 2, 2008.

  1. ora_student

    ora_student Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Hi everyone...

    I am doing a project for school about using oracle about a bank system. the table i have on my sql are customer, holder, account, card type, card, transactions, exchange rate.

    the required triggers i was asked to do where lov's autonumber. i was asked to think about some which are innovative ones. can anyone suggest any innovative trigger. i dont need the syntax. i just need to know what the trigger does in that situation. thanks 4 ur time
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    you should be more specific than that. What triggers do you want to know about?
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Do you mean to say about the Updating the table with Autonumbers (sequences) ? This can also be done using a trigger as below.

    Code (Text):

    SQL> CREATE TABLE EMPLOYEES
      2  (EID           NUMBER(10)    NOT NULL,
      3   ENAME       VARCHAR2(50)  NOT NULL);

    Table created.

    SQL> CREATE SEQUENCE EMPLOYEES_SEQ;

    Sequence created.

    SQL> CREATE OR REPLACE TRIGGER employees_trig
      2  BEFORE INSERT ON employees
      3  FOR EACH ROW
      4  WHEN (new.eid IS NULL)
      5  BEGIN
      6    SELECT employees_seq.NEXTVAL
      7    INTO   :new.eid
      8    FROM   dual;
      9  END;
     10  /

    Trigger created.

    SQL> INSERT INTO EMPLOYEES (ename)  VALUES ('Raj');

    1 row created.

    SQL> INSERT INTO EMPLOYEES (ename)  VALUES ('Tyro');

    1 row created.

    SQL> INSERT INTO EMPLOYEES   VALUES (1000,'Orastudent');

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> SELECT * FROM EMPLOYEES;

           EID ENAME
    ---------- --------------------------------------------------
             1 Raj
             2 Tyro
          1000 Orastudent

    SQL>
     
    This will update the eid with the sequences if the passed eid is NULL. otherwise it will update eid with the value passed. You can do experimentation on this trigger by changing the scenario.

    Hope this is what you are looking for . Have a fun !!!

    .
     
  4. ora_student

    ora_student Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    thanks for your reply, :) actually what i was looking for previously was more about after insert triggers... however i got an excellent solution from simply_dba