CODEWITHSUNDEEP
sqloracle-database
henduo qian
henduo qian

Reputation: 93

Trigger error with after insert

![enter image description here][1]I am creating a trigger that whenever I insert info into my table, I count the total row numbers and print the new added row. Here is my code:

Create or replace trigger TR_everyInsert
After INSERT On PERSONS 
For each row

Declare 
rowNumber int;
PERSON_NAME varchar(30);
gender varchar(30);
color varchar(30);

Begin
select PERSON_NAME,GENDER,COLOR
From PERSONS
Where PERSON_NAME=:new.PERSON_NAME;

select count(*) as rowNumber
from PERSONS;

if inserting then
DBMS_OUTPUT.PUT_LINE ('There are ' || To_CHAR(rowNumber));
DBMS_OUTPUT.PUT_LINE ('New added info is ' || PERSON_NAME || 'with gender ' || 
GENDER || 'with color ' || color);
end if;

end;
/

However, I got compile error saying "into clause expected", what is the problem please?

Upvotes: 0

Views: 60

Answers (2)

Justin Cave
Justin Cave

Reputation: 231651

First, you can't have a PL/SQL block that just executes a SELECT. You need to do something with the data. If you expect the query to return exactly 1 row, do a SELECT INTO. If you expect the query to return more than 1 row, you'd want to open a cursor that you'd iterate over.

Second, in a row-level trigger, you cannot generally query the table itself. You'll generally end up with a mutating table exception (there are some special cases where you can do this but that severely limits your flexibility going forward so it's something that should be avoided). To get row-level information, just use the various columns from your :new pseudo-record. To get the count, you'd realistically want to use a statement-level trigger. Depending on the Oracle version, you could create a compound trigger that has row- and statement-level components as well.

Third, t doesn't really make sense to have an IF inserting statement in a trigger that is only defined on the insert operation. It would only make sense to have that sort of statement if your trigger was defined on multiple operations (say INSERT OR UPDATE) and you wanted to do something different depending on which operation caused the trigger to fire.

Finally, you'll want your local variables to be named something that is distinct from the names of any columns. Most people adopt some sort of naming convention to disambiguate local variables, package global variables, and parameters from column names. I prefer prefixes l_, g_, and p_ for local variables, package global variables, and parameters which is a reasonably common convention in the Oracle community. You may prefer something else.

Something like

-- A row-level trigger prints out the data that is being inserted
Create or replace trigger TR_everyInsert_row
  After INSERT On PERSONS 
  For each row
Begin
  DBMS_OUTPUT.PUT_LINE ('New added info is ' || :new.PERSON_NAME || 
                        ' with gender ' || :new.GENDER || 
                        ' with color ' || :new.color);
end;

-- A statement-level trigger prints out the current row count
Create or replace trigger TR_everyInsert_stmt
  After INSERT On PERSONS 
Declare
  l_cnt integer;
Begin
  select count(*)
    into l_cnt
    from persons; 

  DBMS_OUTPUT.PUT_LINE ('There are ' || To_CHAR(l_cnt) || ' rows.');
end;

Upvotes: 1

tilley31
tilley31

Reputation: 688

The error message is pretty clear. You need to place the result of both your queries INTO the variables you declared:

Create or replace trigger TR_everyInsert
After INSERT On PERSONS 
For each row

Declare 
lv_rowNumber int;
lv-_PERSON_NAME varchar(30);
lv_gender varchar(30);
lv_color varchar(30);

Begin
select PERSON_NAME,GENDER,COLOR
into lv_person_name, lv_gender, lv_color
From PERSONS
Where PERSON_NAME=:new.PERSON_NAME;

select count(*) into lv_rowNumber
from PERSONS;

if inserting then
DBMS_OUTPUT.PUT_LINE ('There are ' || To_CHAR(rowNumber));
DBMS_OUTPUT.PUT_LINE ('New added info is ' || PERSON_NAME || 'with gender ' || 
GENDER || 'with color ' || color);
end if;

end;
/

I would advice you to give your variables different names than your columns. It could make the code confusing to read...

Upvotes: 0

Related Questions