Home » Other » Training & Certification » Help with triggers (oracle10g)
Help with triggers [message #279894] Sun, 11 November 2007 08:59 Go to next message
mrparr
Messages: 9
Registered: October 2007
Junior Member
I am having trouble creating this trigger. I created one but have problems with this one could anyone help.I don't know what I am doing wrong!
I created a table called triggerable_table:
SQL> describe triggerable_table;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_CODE NOT NULL VARCHAR2(Cool
EMPLOYEE_SSN VARCHAR2(9)
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
EMPLOYEE_DOE DATE
EMPLOYEE_DPT VARCHAR2(20)
HOURLY_RATE NUMBER(6,2)
ANNUAL_SALARY NUMBER(8,2)

SQL> CREATE TRIGGER trig1
2 BEFORE INSERT
3 ON triggerable_table
4 FOR EACH ROW
5 DECLARE
6 v_Last_Name:||v_employee_SSN
7 v_RETURN SUBSTR(last_name,1,3)||SUBSTR(employee_SSN,5,5)
8 BEGIN
9 :new.employee_code := :new.Last_Name||''||:new.Employee_SSN;
10
11 END;
12 /
What I needed to do was to create a trigger to compute the employee_code by combining the first three letters of the last_name with the last five numbers of the employye_SSN.
My error message is as follows:
Warning: Trigger created with compilation errors.

SQL> show errors
Errors for TRIGGER TRIG1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/16 PLS-00103: Encountered the symbol ":" when expecting one of the
following:
constant exception <an identifier>
<a double-quoted delimited-identifier> table LONG_ double ref
char time timestamp interval date binary national character
nchar
Re: Help with triggers [message #279897 is a reply to message #279894] Sun, 11 November 2007 09:10 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How many PL/SQL scripts have you written so far? How many of them successfully compiled and executed? What should this do?
DECLARE 
  v_Last_Name:||v_employee_SSN 
  v_RETURN SUBSTR(last_name,1,3)||SUBSTR(employee_SSN,5,5)

This is a link to the Oracle documentation page. One of the Most Popular books is PL/SQL User's Guide and Reference. I'd suggest you to read it.
Re: Help with triggers [message #279899 is a reply to message #279897] Sun, 11 November 2007 09:32 Go to previous messageGo to next message
mrparr
Messages: 9
Registered: October 2007
Junior Member
I admit I am a beginner but I have been trying to figure out where I went wrong and decided to input this varriable

V_lastName,empSSN,:new.employee_code := :new.Last_Name||''||:new.Employee_SSN;
v_RETURN SUBSTR(last_name,1,3)||SUBSTR(employee_SSN,5,5)


I need to be able to insert the first 3 letters of last_name and the last 5 numbers in my table. Should I take this out and do it this way:

CREATE TRIGGER trig1
BEFORE INSERT
ON triggerable_table
FOR EACH ROW
BEGIN
:new.employee_code:= :new.Last_Name||''||:new.Employee_SSN;
END;


Where would I tell the trigger that I need the first 3 letters of last_name and the last 5 numbers to combine and go in employee_code column?
Re: Help with triggers [message #279903 is a reply to message #279899] Sun, 11 November 2007 09:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Application Developer's Guide - Fundamentals
PL/SQL User's Guide and Reference

SQL Reference

Regards
Michel
Re: Help with triggers [message #279904 is a reply to message #279899] Sun, 11 November 2007 09:41 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Would something like this help?
:new.employee_code := SUBSTR(:new.last_name, 1, 3) ||
                      SUBSTR(:new.employee_ssn, -5);
Re: Help with triggers [message #279905 is a reply to message #279904] Sun, 11 November 2007 10:06 Go to previous messageGo to next message
mrparr
Messages: 9
Registered: October 2007
Junior Member
I tried it but the trigger still does not work. Nothing is inserted into the employee_code column.
Look below:
SQL> CREATE TRIGGER trig1
2 BEFORE INSERT
3 ON triggerable_table
4 FOR EACH ROW
5 BEGIN
6 :new.employee_code := SUBSTR(:new.last_name, 1, 3) ||
7 SUBSTR(:new.employee_ssn, -5);
8 END;
9 /

Trigger created.

SQL> INSERT INTO triggerable_table
2 (Employee_Code, Employee_SSN, First_Name, Last_Name,
3 Employee_Doe, Employee_Dpt, Hourly_Rate, Annual_Salary)
4 VALUES
5 ('xxxxxxxx',123567894,'Rachel','Carmichael',
6 TO_DATE('01/02/2005','MM/DD/YYYY'),'Marketing',20,0);

1 row created.

SQL> select*
2 from triggerable_table;

EMPLOYEE EMPLOYEE_ FIRST_NAME LAST_NAME EMPLOYEE_
-------- --------- ------------------------- ------------------------- ---------
EMPLOYEE_DPT HOURLY_RATE ANNUAL_SALARY
-------------------- ----------- -------------
xxxxxxxx 123567894 Rachel Carmichael 02-JAN-05
Marketing 20 41600

Car67894 123567894 Rachel Carmichael 02-JAN-05
Marketing 20 41600
icon10.gif  Re: Help with triggers [message #279906 is a reply to message #279904] Sun, 11 November 2007 10:12 Go to previous messageGo to next message
mrparr
Messages: 9
Registered: October 2007
Junior Member
Yes it did thanks but it doubled it for some reason don't know why I sent the screen shot
Re: Help with triggers [message #279907 is a reply to message #279904] Sun, 11 November 2007 10:19 Go to previous message
mrparr
Messages: 9
Registered: October 2007
Junior Member
Thank you for all your help I am taking Pl/SQL units and Database Administrator II this semester it is the hardest semester I have had!!! You have been a big help and I am thankful.
Previous Topic: convert columns to numbers and calculate % (merged 3 threads)
Next Topic: Certifications in Oracle JDeveloper
Goto Forum:
  


Current Time: Sun Dec 04 00:44:33 CST 2016

Total time taken to generate the page: 0.08591 seconds