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!

Problem in sql, transaction, trigger

Discussion in 'General' started by monkey, Apr 19, 2014.

  1. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!
    Here is the code i wrote to :


    BEGIN TRANSACTION;

    DROP TABLE IF EXISTS PUPIL;

    CREATE TABLE PUPIL(
    PN INTEGER PRIMARY KEY,
    PNAME TEXT
    );

    COMMIT;


    BEGIN TRANSACTION;
    INSERT INTO PUPIL VALUES(1,'AAA');
    INSERT INTO PUPIL VALUES(2,'BBB');
    INSERT INTO PUPIL VALUES(3,'CCC');
    INSERT INTO PUPIL VALUES(4,'DDD');
    COMMIT;

    SELECT * FROM PUPIL;


    BEGIN;
    SAVEPOINT SP;
    INSERT INTO PUPIL VALUES(5,'EEE');
    INSERT INTO PUPIL VALUES(6,'FFF');
    COMMIT;

    BEGIN;
    EXCEPTION
    WHEN OTHERS THEN
    ROLLBACK TO PUPIL
    raise_application_error(-20100, SQLERRM);
    END;

    SELECT * FROM PUPIL;



    Please, can someone tell me what causes error?
    /it is sqlit, hope it is ot such a problem, is it?






    Many thanks!!!
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    It is syntax not for oracle.
    In the orakl there is no operator of the beginning of transaction as MS SQL: BEGIN TRANSACTION.
    Any dml the operator begins transaction implicitly.

    Before oracle block performance itself creates savepoint.
    This block is incorrect


    can be help:

    http://docs.oracle.com/cd/E11882_01/server.112/e25789/transact.htm#CNCPT88953


    Example Creating Savepoints: Example To update the salary for Banda and Greene in the sample table hr.employees, check that the total department salary does not exceed 314,000, then reenter the salary for Greene:


    http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10001.htm#g2227158

    Code (SQL):



    UPDATE employees      SET salary = 7000      WHERE last_name = 'Banda';
     SAVEPOINT banda_sal;  
    UPDATE employees      SET salary = 12000      WHERE last_name = 'Greene';
     SAVEPOINT greene_sal;  
    SELECT SUM(salary) FROM employees;
     ROLLBACK TO SAVEPOINT banda_sal;  
    UPDATE employees      SET salary = 11000      WHERE last_name = 'Greene';  
    COMMIT;