Home » SQL & PL/SQL » SQL & PL/SQL » before insert & instead OF INSERT trigger
| before insert & instead OF INSERT trigger [message #611499] |
Thu, 03 April 2014 03:08  |
 |
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
|
Senior Member |
|
|
Hi all,
I have 2 issues are there when i used the before insert & instead OF INSERT trigger
1) I am not getting values in the created_by , modified_by
SQL> select * from student
2 /
ZIP STUDENT_ID CREATED_BY CREATED_D MODIFIED_B MODIFIED_
---------- ---------- ---------- --------- ---------- ---------
37 03-APR-14 03-APR-14
39 03-APR-14 03-APR-14
CREATE TABLE student
(
zip NUMBER,
student_id NUMBER,
created_by VARCHAR2(10),
created_date DATE,
modified_by VARCHAR2(10),
modified_date DATE
);
CREATE or replace TRIGGER student_bi
BEFORE INSERT ON student
FOR EACH ROW
Declare
V_USER varchar2(10);
V_USER1 varchar2(10);
BEGIN
:NEW.student_id := student_id_seq.NEXTVAL;
:NEW.created_by := V_USER;
:NEW.created_date := SYSDATE;
:NEW.modified_by := V_USER1;
:NEW.modified_date := SYSDATE;
END;
--insert into student values(student_id_seq.NEXTVAL,'10',sysdate,'50',sysdate)
INSERT INTO student
(zip,
student_id,
created_by,
created_date,
modified_by,
modified_date)
VALUES ('',
student_id_seq.NEXTVAL,
'10',
SYSDATE,
'50',
SYSDATE)
2) I am getting bad bind variable error
PLS-00049: bad bind variable'NEW.ZIP'
If the ZIPCODE table has no corresponding record, the trigger should create a new record for the given value of zip before adding a new record to the STUDENT table
view:
-----
CREATE VIEW student_v
AS
SELECT *
FROM student;
Table:
-----
CREATE TABLE zipcode
(
zip NUMBER,
created_by VARCHAR2(10),
created_date DATE,
modified_by VARCHAR2(10),
modified_date DATE
);
CREATE OR replace TRIGGER student_ins
instead OF INSERT ON student_v
FOR EACH ROW
DECLARE
v_zip VARCHAR2(5);
v_user VARCHAR2(10);
v_user1 VARCHAR2(10);
BEGIN
BEGIN
SELECT zip
INTO v_zip
FROM zipcode
WHERE zip = :NEW.zip;
EXCEPTION
WHEN no_data_found THEN
INSERT INTO zipcode
(zip,
created_by,
created_date,
modified_by,
modified_date)
VALUES (:NEW.zip,
:NEW.v_user,
SYSDATE,
:NEW.v_user1,
SYSDATE);
END;
INSERT INTO student
(zip,
student_id,
created_by,
created_date,
modified_by,
modified_date)
VALUES (:NEW.zip,
:NEW.student_id,
v_user,
SYSDATE,
v_user1,
SYSDATE);
END;
select * from student
SELECT * FORM student_v
select * from ZIPCODE
[Updated on: Thu, 03 April 2014 03:14] Report message to a moderator |
|
|
|
| Re: before insert & instead OF INSERT trigger [message #611500 is a reply to message #611499] |
Thu, 03 April 2014 03:17  |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
1/ ALWAYS post your Oracle version
2/ ALWAYS post your SQL*Plus session
3/ I have not the error:
SQL> create sequence STUDENT_ID_SEQ;
Sequence created.
SQL> CREATE TABLE student
2 (
3 zip NUMBER,
4 student_id NUMBER,
5 created_by VARCHAR2(10),
6 created_date DATE,
7 modified_by VARCHAR2(10),
8 modified_date DATE
9 );
Table created.
SQL> CREATE or replace TRIGGER student_bi
2 BEFORE INSERT ON student
3 FOR EACH ROW
4 Declare
5 V_USER varchar2(10);
6 V_USER1 varchar2(10);
7 BEGIN
8 :NEW.student_id := student_id_seq.NEXTVAL;
9 :NEW.created_by := V_USER;
10 :NEW.created_date := SYSDATE;
11 :NEW.modified_by := V_USER1;
12 :NEW.modified_date := SYSDATE;
13 END;
14 /
Trigger created.
SQL> INSERT INTO student
2 (zip,
3 student_id,
4 created_by,
5 created_date,
6 modified_by,
7 modified_date)
8 VALUES ('',
9 student_id_seq.NEXTVAL,
10 '10',
11 SYSDATE,
12 '50',
13 SYSDATE)
14 /
1 row created.
4/ I stopped reading there.
|
|
|
|
|
|
| Re: before insert & instead OF INSERT trigger [message #611506 is a reply to message #611503] |
Thu, 03 April 2014 03:24 |
|
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
|
Senior Member |
|
|
Hi sir,
I am using oracle 10g.
SQL> CREATE OR replace TRIGGER student_ins
2 instead OF INSERT ON student_v
3 FOR EACH ROW
4 DECLARE
5 v_zip VARCHAR2(5);
6 v_user VARCHAR2(10);
7 v_user1 VARCHAR2(10);
8 BEGIN
9 BEGIN
10 SELECT zip
11 INTO v_zip
12 FROM zipcode
13 WHERE zip = :NEW.zip;
14 EXCEPTION
15 WHEN no_data_found THEN
16 INSERT INTO zipcode
17 (zip,
18 created_by,
19 created_date,
20 modified_by,
21 modified_date)
22 VALUES (:NEW.zip,
23 :NEW.v_user,
24 SYSDATE,
25 :NEW.v_user1,
26 SYSDATE);
27 END;
28
29 INSERT INTO student
30 (zip,
31 student_id,
32 created_by,
33 created_date,
34 modified_by,
35 modified_date)
36 VALUES (:NEW.zip,
37 :NEW.student_id,
38 v_user,
39 SYSDATE,
40 v_user1,
41 SYSDATE);
42 END;
43 /
Warning: Trigger created with compilation errors.
SQL> show error
Errors for TRIGGER STUDENT_INS:
LINE/COL ERROR
-------- -----------------------------------------------------------------
10/22 PLS-00049: bad bind variable 'NEW.ZIP'
19/24 PLS-00049: bad bind variable 'NEW.ZIP'
20/24 PLS-00049: bad bind variable 'NEW.V_USER'
22/24 PLS-00049: bad bind variable 'NEW.V_USER1'
33/18 PLS-00049: bad bind variable 'NEW.ZIP'
SQL> 10
10* SELECT zip
SQL> 19
19* created_date,
SQL> 20
20* modified_by,
SQL> 22
22* VALUES (:NEW.zip,
SQL> 33
33* created_date,
SQL>
did you why i am not populate the created_by and modify_by columns.
SQL> INSERT INTO student
2 (zip,
3 student_id,
4 created_by,
5 created_date,
6 modified_by,
7 modified_date)
8 VALUES ('',
9 student_id_seq.NEXTVAL,
10 '10',
11 SYSDATE,
12 '50',
13 SYSDATE)
14 /
1 row created.
SQL> commit;
Commit complete.
SQL> select * from student;
ZIP STUDENT_ID CREATED_BY CREATED_D MODIFIED_B MODIFIED_
---------- ---------- ---------- --------- ---------- ---------
37 03-APR-14 03-APR-14
39 03-APR-14 03-APR-14
41 03-APR-14 03-APR-14
|
|
|
|
|
|
|
|
| Re: before insert & instead OF INSERT trigger [message #611514 is a reply to message #611510] |
Thu, 03 April 2014 04:01 |
|
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
|
Senior Member |
|
|
1st Trigger working fine.
SQL> CREATE or replace TRIGGER student_bi
2 BEFORE INSERT ON student
3 FOR EACH ROW
4 Declare
5 V_USER varchar2(10);
6 V_USER1 varchar2(10);
7 BEGIN
8 :NEW.student_id := student_id_seq.NEXTVAL;
9 :NEW.created_by := V_USER;
10 :NEW.created_date := SYSDATE;
11 :NEW.modified_by := V_USER1;
12 :NEW.modified_date := SYSDATE;
13 END;
14 /
Trigger created.
I am Talking about 2nd one Trigger(student_ins)
Quote:What values do you expect created_by and modified_by to be populated with and why?
SQL> INSERT INTO student
2 (zip,
3 student_id,
4 created_by,
5 created_date,
6 modified_by,
7 modified_date)
8 VALUES ('',
9 student_id_seq.NEXTVAL,
10 '20', ---created_by
11 SYSDATE,
12 '40', ---modified_by
13 SYSDATE)
14 /
1 row created.
SQL> select * from student
2 /
ZIP STUDENT_ID CREATED_BY CREATED_D MODIFIED_B MODIFIED_
---------- ---------- ---------- --------- ---------- ---------
37 03-APR-14 03-APR-14
39 03-APR-14 03-APR-14
41 03-APR-14 03-APR-14
43 03-APR-14 03-APR-14
Please observe the above CREATED_BY & MODIFIED_BY Columns means ,why it is not nserted the values of the 2 columns.
[Updated on: Thu, 03 April 2014 04:02] Report message to a moderator |
|
|
|
|
|
| Re: before insert & instead OF INSERT trigger [message #611516 is a reply to message #611515] |
Thu, 03 April 2014 04:22 |
|
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
|
Senior Member |
|
|
SQL> CREATE or replace TRIGGER student_bi
2 BEFORE INSERT ON student
3 FOR EACH ROW
4 Declare
5 created_by varchar2(10);
6 modified_by varchar2(10);
7 BEGIN
8 :NEW.student_id := student_id_seq.NEXTVAL;
9 :NEW.created_by := created_by;
10 :NEW.created_date := SYSDATE;
11 :NEW.modified_by := modified_by;
12 :NEW.modified_date := SYSDATE;
13 END;
14 /
Trigger created.
SQL> INSERT INTO student
2 (zip,
3 student_id,
4 created_by,
5 created_date,
6 modified_by,
7 modified_date)
8 VALUES ('',
9 student_id_seq.NEXTVAL,
10 '20', ---created_by
11 SYSDATE,
12 '40', ---modified_by
13 SYSDATE)
14 /
1 row created.
SQL> commit;
Commit complete.
SQL> select * from student;
ZIP STUDENT_ID CREATED_BY CREATED_D MODIFIED_B MODIFIED_
---------- ---------- ---------- --------- ---------- ---------
63 03-APR-14 03-APR-14
SQL> CREATE or replace TRIGGER student_bi
2 BEFORE INSERT ON student
3 FOR EACH ROW
4
5 BEGIN
6 :NEW.student_id := student_id_seq.NEXTVAL;
7 :NEW.created_by := USER;
8 :NEW.created_date := SYSDATE;
9 :NEW.modified_by := USER;
10 :NEW.modified_date := SYSDATE;
11 END;
12 /
Trigger created.
SQL> INSERT INTO student
2 (zip,
3 student_id,
4 created_by,
5 created_date,
6 modified_by,
7 modified_date)
8 VALUES ('',
9 student_id_seq.NEXTVAL,
10 USER, ---created_by
11 SYSDATE,
12 USER, ---modified_by
13 SYSDATE)
14 /
1 row created.
SQL> commit;
Commit complete.
SQL> select * from student;
ZIP STUDENT_ID CREATED_BY CREATED_D MODIFIED_B MODIFIED_
---------- ---------- ---------- --------- ---------- ---------
63 03-APR-14 03-APR-14
65 APPS 03-APR-14 APPS 03-APR-14
|
|
|
|
|
|
|
|
| Re: before insert & instead OF INSERT trigger [message #611519 is a reply to message #611518] |
Thu, 03 April 2014 04:32 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
The sqlplus version (generally) doesn't matter. The DB version does.
Your DB is 11.1.0.7.0
Also please realize that as long as the trigger assigns values to columns the values you give to those columns in the insert statement are irrelevant.
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 21:27:50 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
