CODEWITHSUNDEEP
sqloracle-databaseplsqltriggerscompiler-errors
Anya
Anya

Reputation: 31

SQL TRIGGER (Compilation errors)

I'm writing a trigger in SQL for the first time. Can anyone please tell me what am I writing wrong in this code? 

SQL> CREATE OR REPLACE TRIGGER Course_Allocation_After_Update
2  AFTER UPDATE
3  ON COURSE_ALLOCATION
4  FOR EACH ROW
5  DECLARE
6  v_user_id varchar2(20);
7  BEGIN
8  --Finding User who is performing the update in the table
9  SELECT USER
10  INTO v_user_id
11  FROM dual;
12  --Inserting Information Into Staging Table (Log Table)
13  IF UPDATING(STU_ID)   
14  THEN
15  INSERT INTO STAGING_TABLE
16  (user_id,old_value,new_value,date_when_updated,time_when_udated)
17  VALUES
18  (v_user_id,:old.STU_ID,:new.STU_ID,sysdate,systime)
19  END IF;
20  END;
21  /
Warning: Trigger created with compilation errors.

These are the errors:
11/1 PL/SQL: SQL Statement ignored 14/53 PL/SQL: ORA-00933: SQL command not properly ended 16/4 PLS-00103: Encountered the symbol ";" when expecting one of the following: if

Upvotes: 0

Views: 325

Answers (2)

wieseman
wieseman

Reputation: 176

Try this one:

CREATE OR REPLACE TRIGGER Course_Allocation_After_Update 
AFTER UPDATE ON COURSE_ALLOCATION 
referencing old as old new as new
FOR EACH ROW 

DECLARE 
    v_STU_ID varchar2(20); 

BEGIN --Finding User who is performing the update in the table 
    SELECT STU_ID 
    INTO v_STU_ID 
    FROM COURSE_ALLOCATION; 

    --Inserting Information Into Staging Table (Log Table) 
    IF UPDATING('STU_ID') 
    THEN 
        INSERT INTO STAGING_TABLE (user_id,old_value,new_value,date_when_updated) 
        VALUES (USER,:old.v_STU_ID,:new.v_STU_ID,sysdate); 
    END IF; 
 END; 
 /

Futher it seems to me that:

SELECT STU_ID 
INTO v_STU_ID 
FROM COURSE_ALLOCATION;

can not be good, because you will select all the stu_id records from the course_allocation table. And that will give you a "too_many_rows" error. How does the course_allocation table look like?

Upvotes: 1

Michael Piankov
Michael Piankov

Reputation: 1997

  1. Your forget semicolon after insert clause;
  2. systime - not exists in Oracle . SYSDATE - already contain information about time. If you want to separate it you may use trunc(sysdate) - return only date and to_char(sysdate,'HH24:MI:SS') - return string with time. Or may be you mean systimestamp
  3. (optional) why you need to select user into variable? Just use it as value

My version code of trigger:

 CREATE OR REPLACE TRIGGER Course_Allocation_After_Update
 AFTER UPDATE
 ON COURSE_ALLOCATION
 FOR EACH ROW
 DECLARE
 v_user_id varchar2(20);
 BEGIN
 --Inserting Information Into Staging Table (Log Table)
 IF UPDATING(STU_ID)   
 THEN
 INSERT INTO STAGING_TABLE
 (user_id,old_value,new_value,date_when_updated)
 VALUES
 (USER,:old.STU_ID,:new.STU_ID,sysdate);
 END IF;
 END;
 /

Upvotes: 2

Related Questions