Such as: the emp table,
When a new data with ename fields in the table, if new data ename in the table already exists, then in addition to the primary key to other fields are the same as the original data, and insert. If it does not have the same ename field values, don't do processing, directly inserted into the
For example, to create a empno for 9988, ename for SMITH's data, the other fields do not write, take the default, after the trigger, the new data for [empno: 9988, ename: SMITH, job: CLERK, MGR: 7902, hiredate: 17 - DEC - 80, sal: 800, comm: (null), deptno: 20] (empno is different, only the rest of the field is the same)
Don't know how to write such a trigger, a great god to solve, if the statement execution is best

CodePudding user response:

You ever written a similar trigger, can draw lessons from the trigger statement? Thank you very much

CodePudding user response:

The building Lord this requirement, consider change the insert to the merge statement, very suitable for your needs, now

Merge into syntax, baidu first, have a question to ask,

CodePudding user response:

Thank you very much for the second floor to answer, I'll go search under the usage of the merge

CodePudding user response:

 
The create or replace the trigger tri_insert_empyee 
After 
Insert 
On an emp 
For each row 
Declare 
The begin 
- perform when insert 
If the insert then 
The merge into an emp e 
Using (
- search new insert data (part of the field is empty, this screening) 
Select * from emp where sal=null 
Ee) 
On (
- already empno is the same in the two tables, and wage salary isn't as empty as conditions in the table e 
E.e name=ee. Ename and 
E.s al!=null 
) 
The when matched then 
- when matching, the new data fields in the assignment 
Update the set (ee. Sal=e.s al) 
When not matched then 
- does not match the case, then insert directly, not fill in the fields to take an empty value ( not sure here, because when the trigger after insert trigger, the following data will lead to repeat the insert? 
 )Insert (e.e mpno, e.e name, e.j ob, e.m gr, e.h iredate, e.s al, e.com m, e.d eptno) values (ee) empno, ee, ename, ee, job, ee. MGR, ee, hiredate, ee, sal, ee.com m, ee, deptno); 
End the if; 
end; 

ERROR: LINE/COL ERROR
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
3/8 PLS - 00103: a symbol of "INSERT" when need to be one of the following: (- + case mod new not null & lt; The an identifier> The continue avg count current exists Max min the prior SQL stddev sum variance execute forall the merge time timestamp interval date & lt; A string literal with character set specification> Error: check the compiler log

CodePudding user response:

refer to the second floor wmxcn2000 response:

the building this requirement, consider change the insert to the merge statement, very suitable for your needs now,

The merge into syntax, baidu first, there is a problem to ask again,

Predecessors, writes, 4/f, excuse me, what is going wrong, the orcale error was some look not to understand, and I wrote the ideas right?

CodePudding user response:

Not merge to the trigger, but with the merge replace the original insert statement, also in less than a trigger,

CodePudding user response:

refer to 6th floor wmxcn2000 response:

not merge to the trigger, but with the merge replace the original insert statement, also in less than a trigger,

Actual it is someone else's data by insert into my watch, I can't control, originally want to be a trigger, in the part of the data for the new import over field judgment, I if there is the same record in the table, put the new imported record update to replace by other parts of the field, it seems the merge of the function is not very suitable for me.

CodePudding user response:

Well, it seems is not applicable to this merge syntax,

You can only consider the portfolio trigger, thinking about the following:
1, in before the row record all new insert data,
2, in the after STMT handle all duplicate data,

CodePudding user response:

BEFOR directly INSERT triggers can now

First select * from table into variable where empname=: new. Empname;
And then: new. * * * :=variable A;
: new. * * * : B=variables;
And so on can be

CodePudding user response:

Best empname this field an index or table of the large amount of data, the low efficiency of the trigger