Oracle trigger problems-CodePudding

Create a trigger, when a user to form to insert a row, will the department number of data on the basis of the original plus 1

CodePudding user response:

How to write code with PL/SQL statements

CodePudding user response:

 
SQL> 
SQL> Col dept_id a10 format; 
SQL> Col num a10 format; 
SQL> The create table dept (dept_id int, dept_name varchar (10), num int); 
The Table created 
SQL> The create table emp (emp_id int, dept_id int, emp_name varchar (10)); 
The Table created 
SQL> Create the trigger tri_emp_ins 
2 before the insert on emp 
3 for each row 
4 the begin 
5 update dept set num=num + 1 where dept_id=: new. Dept_id; 
6 the end; 
7/
The Trigger created 
SQL> Insert into dept values (100, 'Dev, 0); 
1 row inserted 
SQL> Select * from dept. 
DEPT_ID DEPT_NAME NUM 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
100 Dev 0 
SQL> Insert into emp values (1, 100, "zhangsan"); 
1 row inserted 
SQL> Select * from dept. 
DEPT_ID DEPT_NAME NUM 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
100 Dev 1 
SQL> Drop table dept purge; 
Table dropped 
SQL> Drop table emp purge; 
Table dropped 

SQL>

CodePudding user response:

refer to the second floor wmxcn2000 response:

 
SQL> 
SQL> Col dept_id a10 format; 
SQL> Col num a10 format; 
SQL> The create table dept (dept_id int, dept_name varchar (10), num int); 
The Table created 
SQL> The create table emp (emp_id int, dept_id int, emp_name varchar (10)); 
The Table created 
SQL> Create the trigger tri_emp_ins 
2 before the insert on emp 
3 for each row 
4 the begin 
5 update dept set num=num + 1 where dept_id=: new. Dept_id; 
6 the end; 
7/
The Trigger created 
SQL> Insert into dept values (100, 'Dev, 0); 
1 row inserted 
SQL> Select * from dept. 
DEPT_ID DEPT_NAME NUM 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
100 Dev 0 
SQL> Insert into emp values (1, 100, "zhangsan"); 
1 row inserted 
SQL> Select * from dept. 
DEPT_ID DEPT_NAME NUM 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
100 Dev 1 
SQL> Drop table dept purge; 
Table dropped 
SQL> Drop table emp purge; 
Table dropped 

SQL>

CodePudding user response:

Great spirit to understand the wrong I mean, I am not update the department number, but when a user insert a row to form, department number, on the basis of the original plus 1 (such as: there are two departments in this table, numbered 1 and 2, the next time before inserting data, the new department number of data set to 3)

CodePudding user response:

refer to the second floor wmxcn2000 response:

 
SQL> 
SQL> Col dept_id a10 format; 
SQL> Col num a10 format; 
SQL> The create table dept (dept_id int, dept_name varchar (10), num int); 
The Table created 
SQL> The create table emp (emp_id int, dept_id int, emp_name varchar (10)); 
The Table created 
SQL> Create the trigger tri_emp_ins 
2 before the insert on emp 
3 for each row 
4 the begin 
5 update dept set num=num + 1 where dept_id=: new. Dept_id; 
6 the end; 
7/
The Trigger created 
SQL> Insert into dept values (100, 'Dev, 0); 
1 row inserted 
SQL> Select * from dept. 
DEPT_ID DEPT_NAME NUM 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
100 Dev 0 
SQL> Insert into emp values (1, 100, "zhangsan"); 
1 row inserted 
SQL> Select * from dept. 
DEPT_ID DEPT_NAME NUM 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
100 Dev 1 
SQL> Drop table dept purge; 
Table dropped 
SQL> Drop table emp purge; 
Table dropped 

SQL>

CodePudding user response:

Take, for example;

CodePudding user response:

Department number department name
total product model through inspection1 production department XT - 03 650 730
2 production department 2 XT - 03, 680, 780
Create a trigger, when a user to form to insert a row, will the bank data of department number, on the basis of the original plus 1
(such as: there are two departments in this table, numbered 1 and 2, the next time before inserting data, the new department number of data set to 3)

CodePudding user response:

The

reference 7 floor qq_32118827 response:

department number department name
total product model through inspection1 production department XT - 03 650 730
2 production department 2 XT - 03, 680, 780
Create a trigger, when a user to form to insert a row, will the bank data of department number, on the basis of the original plus 1
(such as: there are two departments in this table, numbered 1 and 2, the next time before inserting data, the new department number of data set to 3)

Still didn't understand, you said once, before the insert user department table looks like, after inserting data, what department table and table, with excel

CodePudding user response:

nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull