Results 1 to 5 of 5

Thread: trigger help

  1. #1
    Join Date
    Nov 2008
    Posts
    20

    Unanswered: trigger help

    Hello,
    I'm trying to create a trigger as

    Code:
    CREATE OR REPLACE TRIGGER changeEmployeeTrigger
    AFTER INSERT OR DELETE OR UPDATE ON Employees
    FOR EACH ROW
    	
    BEGIN
    		
           INSERT INTO ChangeEmployee VALUES(
    	SELECT sys_context('USERENV','CURRENT_USER') FROM dual,
    	'Employees',
    	SELECT sys_context('USERENV','ACTION') FROM dual,
    	SELECT sys_date FROM dual);
    END;
    
    .
    RUN;
    but why i get compilation errors:
    5/7 PL/SQL: SQL Statement ignored
    5/19 PL/SQL: ORA-00942: table or view does not exist
    Last edited by khdani; 02-01-09 at 12:16.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The CREATE OR REPLACE is not SQL syntax, neither is the DUAL table, SYS_CONTEXT or SYS_DATE. A trigger is also for a single specific DML operation and not for a combination.

    You can do this like so:
    Code:
    CREATE TRIGGER changeEmployeeTrigger
       AFTER INSERT ON Employees
       FOR EACH ROW
       INSERT INTO ChangeEmployee
       VALUES CURRENT USER, 'INSERT', CURRENT DATE;
    Last edited by stolze; 02-03-09 at 08:07.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    I suppose the subselects need to be enclosed in parenthesis...

    I.e
    INSERT INTO ChangeEmployee VALUES(
    (SELECT sys_context('USERENV','CURRENT_USER') FROM dual),
    'Employees', ...


    Thats the ANSI/ISO SQL way... Check out the SQL Validator next time you have syntax problems. Mimer SQL Developers - Mimer SQL-2003 Validator
    A great time saver when writing standard compliant SQL.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Drop all the redundant SELECTs, and spell SYSDATE correctly:

    Code:
    CREATE OR REPLACE TRIGGER changeEmployeeTrigger
    AFTER INSERT OR DELETE OR UPDATE ON Employees
    FOR EACH ROW
    BEGIN
           INSERT INTO ChangeEmployee VALUES(
    	sys_context('USERENV','CURRENT_USER'),
    	'Employees',
    	sys_context('USERENV','ACTION'),
    	sysdate );
    END;
    /

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by stolze
    The CREATE OR REPLACE is not SQL syntax, neither is the DUAL table, SYS_CONTEXT or SYS_DATE. A trigger is also for a single specific DML operation and not for a combination.
    All these comments may be true for some DBMS, but not for Oracle which the poster is apparently using. I have moved this thread into the Oracle forum.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •