create OR REPLACE the trigger trans_code_t 
After 
Insert 
On EMR_ICH 
FOR EACH row. 
The BEGIN 
IF inserting THEN 
Trans_code_p (EMR); - call a stored procedure 
END IF; 
END; 


The create or replace procedure trans_code_p (TABLE_NAME in varchar (100)) 

As 
VORG_CODE VARCHAR (100); 
V_SQL VARCHAR2 (1000); 
NEWORG_CODE VARCHAR (100); 

The Begin 
VORG_CODE:=(SELECT ORG_CODE FROM TABLE_NAME where rownum & lt; 2); - access to insert records ORG_CODE the value of the field 
IF VORG_CODE='371721 a30001 - insert the value of the 
THEN 
NEWORG_CODE:='173023809'; - setting the new value 
V_SQL:='UPDATE' | | TABLE_NAME | | 'SET' | | 'ORG_CODE' | | '=' | | NEWORG_CODE; 
The EXECUTE IMMEDIATE (V_SQL); 
END; 
The End; 

An error, a lot of wrong

CodePudding user response:

Add Spaces before and after the set look again

CodePudding user response:

V_SQL:='UPDATE' | | TABLE_NAME | | 'SET' | | 'ORG_CODE=' ' '| | NEWORG_CODE | |' where there should be a condition ';

CodePudding user response:

Stored procedures and triggers the creation of the script must be executed twice, otherwise complains,

 
The create or replace procedure trans_code_p (table_name in varchar (100)) 
As 
Vorg_code varchar (100); 
V_sql varchar2 (1000); 
Neworg_code varchar (100); 
The begin 
V_sql:='select org_code from' | | table_name | | 'where rownum & lt; 2 '. 

The execute immediate v_sql into vorg_code; 

If vorg_code='371721 a30001 then 
Neworg_code:='173023809'; - setting the new value 
V_sql:='update' | | table_name | | 'set org_code' | | '=' | | neworg_code; 
The execute immediate v_sql; 
commit; 
The end; 

The end; 

CodePudding user response:

reference qq646748739 reply: 3/f

stored procedures and triggers the creation of the script must be executed twice, otherwise an error,

 
The create or replace procedure trans_code_p (table_name in varchar (100)) 
As 
Vorg_code varchar (100); 
V_sql varchar2 (1000); 
Neworg_code varchar (100); 
The begin 
V_sql:='select org_code from' | | table_name | | 'where rownum & lt; 2 '. 

The execute immediate v_sql into vorg_code; 

If vorg_code='371721 a30001 then 
Neworg_code:='173023809'; - setting the new value 
V_sql:='update' | | table_name | | 'set org_code' | | '=' | | neworg_code; 
The execute immediate v_sql; 
commit; 
The end; 

The end; 

Thank you for your answer, I want to insert the data org_code for judging the if, I feel rownum & lt; 2 is no good

CodePudding user response:

In fact, there is no need to write a stored procedure alone, have to do is write together,
Your trigger is row-level trigger, if don't want to separate out the stored procedure, and it is also suggested that the: new.ORG _CODE this value in the form of parameters are passed to the stored procedure, rather than to get again