| Help with triggers [message #279894] |
Sun, 11 November 2007 08:59  |
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(
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 #279899 is a reply to message #279897] |
Sun, 11 November 2007 09:32  |
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 #279905 is a reply to message #279904] |
Sun, 11 November 2007 10:06 |
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
|
|
|
|
|
|
| Re: Help with triggers [message #279907 is a reply to message #279904] |
Sun, 11 November 2007 10:19 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
