Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What is the syntax to use a Select statement inside a PL/SQL Trigger?

This is what I currently have:

CREATE OR REPLACE TRIGGER MYTRIGGER
AFTER INSERT ON SOMETABLE
FOR EACH ROW    

DECLARE
 v_emplid varchar2(10);    

BEGIN
 SELECT
  personnum into v_emplid
 FROM PERSON
 WHERE PERSONID = :new.EMPLOYEEID;

dbms_output.put(v_emplid);

/* INSERT INTO SOMEOTHERTABLE USING v_emplid and some of the other values from the trigger table*/

END MYTRIGGER;    

DBA_ERRORS has this error: PL/SQL: ORA-00923: FROM keyword not found where expected

like image 925
Equistatic Avatar asked Oct 14 '08 20:10

Equistatic


People also ask

What is the syntax of trigger in PL SQL?

Syntax for creating trigger:CREATE [OR REPLACE ] TRIGGER trigger_name. {BEFORE | AFTER | INSTEAD OF } {INSERT [OR] | UPDATE [OR] | DELETE} [OF col_name]

Can we use SELECT statement in trigger in SQL?

The trigger event that initiates the trigger action can be an INSERT, DELETE, UPDATE, or a SELECT statement. The MERGE statement can also be the triggering event for an UPDATE, DELETE, or INSERT trigger.

What is the syntax of trigger?

Explanation of syntax:create trigger [trigger_name]: Creates or replaces an existing trigger with the trigger_name. [before | after]: This specifies when the trigger will be executed. {insert | update | delete}: This specifies the DML operation.

How do you SELECT a trigger?

Navigate to triggers folder at the table level, select the trigger, Right click on trigger and Click on Enable/Disable to Enable or disable the trigger using SSMS. Disabling specific SQL Server trigger on a table using T-SQL. Enabling specific trigger on the table using T-SQL.


1 Answers

1) There must be something else to your example because that sure seems to work for me

SQL> create table someTable( employeeid number );

Table created.

SQL> create table person( personid number, personnum varchar2(10) );

Table created.

SQL> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE TRIGGER MYTRIGGER
  2    AFTER INSERT ON SOMETABLE
  3    FOR EACH ROW
  4  DECLARE
  5   v_emplid varchar2(10);
  6  BEGIN
  7   SELECT personnum
  8     into v_emplid
  9     FROM PERSON
 10    WHERE PERSONID = :new.EMPLOYEEID;
 11    dbms_output.put(v_emplid);
 12    /* INSERT INTO SOMEOTHERTABLE USING v_emplid and some of the other values
 from the trigger table*/
 13* END MYTRIGGER;
 14  /

Trigger created.

SQL> insert into person values( 1, '123' );

1 row created.

SQL> insert into sometable values( 1 );

1 row created.

2) You probably want to declare V_EMPLID as being of type Person.PersonNum%TYPE so that you can be certain that the data type is correct and so that if the data type of the table changes you won't need to change your code.

3) I assume that you know that your trigger cannot query or update the table on which the trigger is defined (so no queries or inserts into someTable).

like image 147
Justin Cave Avatar answered Oct 06 '22 16:10

Justin Cave