PL SQL लूप टेबल डालने और अपडेट करने के बाद

show err एक अद्भुत चीज है। (मैंने आपके द्वारा उपयोग की जाने वाली डमी टेबल बनाई हैं)।

SQL> CREATE OR REPLACE TRIGGER broker_level_trigger
  2    AFTER INSERT ON appointment
  3  DECLARE
  4    counter integer := 1;
  5  BEGIN
  6    for o in (SELECT * FROM broker)
  7    loop
  8      SELECT COUNT(appointment.broker_id)
  9        INTO app_number
 10        FROM appointment INNER JOIN broker
 11          ON broker.broker_id = appointment.broker_id
 12        WHERE broker.broker_id = counter;
 13
 14      IF app_number > 15 THEN
 15         UPDATE broker SET
 16           broker_level = 'gold'
 17           WHERE broker_id = counter;
 18         counter := counter + 1;
 19
 20    end loop;
 21  end;
 22  /

Warning: Trigger created with compilation errors.

SQL> show err
Errors for TRIGGER BROKER_LEVEL_TRIGGER:

LINE/COL ERROR
-------- -----------------------------------------------------------------
18/7     PLS-00103: Encountered the symbol "LOOP" when expecting one of
         the following:
         if

अगर आप करीब से देखें, तो आप देखेंगे कि IF अपनी END IF से चूक गया है। आइए इसे जोड़ें:

SQL> CREATE OR REPLACE TRIGGER broker_level_trigger
  2    AFTER INSERT ON appointment
  3  DECLARE
  4    counter integer := 1;
  5  BEGIN
  6    for o in (SELECT * FROM broker)
  7    loop
  8      SELECT COUNT(appointment.broker_id)
  9        INTO app_number
 10        FROM appointment INNER JOIN broker
 11          ON broker.broker_id = appointment.broker_id
 12        WHERE broker.broker_id = counter;
 13
 14      IF app_number > 15 THEN
 15         UPDATE broker SET
 16           broker_level = 'gold'
 17           WHERE broker_id = counter;
 18         counter := counter + 1;
 19      END IF;                          --> missing
 20    end loop;
 21  end;
 22  /

Warning: Trigger created with compilation errors.

SQL> show err
Errors for TRIGGER BROKER_LEVEL_TRIGGER:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/5      PL/SQL: SQL Statement ignored
7/12     PLS-00201: identifier 'APP_NUMBER' must be declared
8/7      PL/SQL: ORA-00904: : invalid identifier
12/5     PL/SQL: Statement ignored
12/8     PLS-00201: identifier 'APP_NUMBER' must be declared

APP_NUMBER अब गायब है; आप इसका इस्तेमाल करते हैं, लेकिन इसे कभी घोषित नहीं किया। चलो अब हम यह करें:

SQL> CREATE OR REPLACE TRIGGER broker_level_trigger
  2    AFTER INSERT ON appointment
  3  DECLARE
  4    counter integer := 1;
  5    app_number number;                   --> missing
  6  BEGIN
  7    for o in (SELECT * FROM broker)
  8    loop
  9      SELECT COUNT(appointment.broker_id)
 10        INTO app_number
 11        FROM appointment INNER JOIN broker
 12          ON broker.broker_id = appointment.broker_id
 13        WHERE broker.broker_id = counter;
 14
 15      IF app_number > 15 THEN
 16         UPDATE broker SET
 17           broker_level = 'gold'
 18           WHERE broker_id = counter;
 19         counter := counter + 1;
 20      END IF;                          --> missing
 21    end loop;
 22  end;
 23  /

Trigger created.

SQL>

यही होगा।

यदि आप SQL*Plus लेकिन किसी अन्य टूल का उपयोग नहीं करते हैं, तो आप हमेशा user_errors क्वेरी कर सकते हैं:

Warning: Trigger created with compilation errors.

SQL> select line, position, text from user_errors where name = 'BROKER_LEVEL_TRIGGER' order by sequence;

 LINE  POSITION TEXT
----- --------- ------------------------------------------------------------
    8        12 PLS-00201: identifier 'APP_NUMBER' must be declared
    9         7 PL/SQL: ORA-00904: : invalid identifier
    7         5 PL/SQL: SQL Statement ignored
   13         8 PLS-00201: identifier 'APP_NUMBER' must be declared
   13         5 PL/SQL: Statement ignored

SQL>