Home » SQL & PL/SQL » SQL & PL/SQL » create trigger error in SQL
create trigger error in SQL [message #658313] Fri, 09 December 2016 02:10 Go to next message
equa
Messages: 1
Registered: December 2016
Junior Member
Hi,

I have a problem about sql trigger in SQL Developer.

CREATE OR REPLACE EDITIONABLE TRIGGER "AC_ACCRUED_PREMIUM_GEN_TRG" 
AFTER INSERT ON AC_ACCRUED_PREMIUM_GEN  
FOR EACH ROW
BEGIN
	          INSERT INTO AC_ACCRUED_PREMIUM_PLAN_GEN
                SELECT 
                	:NEW.ISLEM_NO AS PREMIUM_GEN_ID ,
                	:NEW.KAYNAK_ID AS TRANSACTION_ID,
                	P.ID AS POLICY_ID,
                	P_HEALTH.GROUP_HEALTH_PLAN_ID,
                	P.COMPANY_SHARE_PERCENT,
                	PA.REINSURANCE_COMMISSION_RATE,
                	SUM(ENT.AMOUNT) AS TOTAL_PREMIUM_OF_PLAN,
                	(SUM(ENT.AMOUNT) * P.COMPANY_SHARE_PERCENT/100) AS COMPANY_SHARE_AMOUNT,
                	(SUM(ENT.AMOUNT) * PA.REINSURANCE_COMMISSION_RATE /100) AS REINSURANCE_COMMISSION_AMOUNT,
                	PA.AS_ISSUE_DATE,
                	SYSDATE AS CREATE_DATE
                FROM  AC_ENTRY ENT   
                INNER JOIN P_POLICY P ON P.ID = ENT.POLICY_ID AND ENT.SUPER_ENTRY_TYPE IN (3,4) AND ENT.TRANSACTION_TYPE_ID IN (1,50,51)  
                INNER JOIN P_POLICY_ANDL PA ON PA.ID = P.ID
                INNER JOIN P_POLICY_LOB P_LOB ON P.ID = P_LOB.POLICY_ID 
                INNER JOIN P_POLICY_LOB_TO_LOB_ASSET L_ASSET ON L_ASSET.POLICY_LOB_ID = P_LOB.ID
                INNER JOIN P_POLICY_LOB_ASSET P_ASSET ON P_ASSET.ID = L_ASSET.LOB_ASSET_ID AND P_ASSET.ORIGINAL_LOB_ASSET_ID = ENT.ORIGINAL_LOB_ASSET_ID
                INNER JOIN AS_ASSET A_ASSET ON A_ASSET.ID = P_ASSET.LOB_ASSET_ID
                INNER JOIN AS_PERSON A_PERSON ON  A_ASSET.ID=A_PERSON.ASSET_ID 
                INNER JOIN AS_PERSON_HEALTH_ANDL P_HEALTH ON A_PERSON.ID=P_HEALTH.PERSON_ID   
                WHERE ENT.TRANSACTION_ID = :NEW.KAYNAK_ID
                GROUP BY  
                		 P.ID, 
                	 	 P_HEALTH.GROUP_HEALTH_PLAN_ID,
                		 P.COMPANY_SHARE_PERCENT,
                		 PA.REINSURANCE_COMMISSION_RATE,
                		 PA.AS_ISSUE_DATE;
END;

I run it an get error.

Error is that:

00922. 00000 - "missing or invalid option"
Error starting at line 34 in command:
END
Error report:
Unknown Command


[mod-edit: pink color tags replaced with code tags by bb]

[Updated on: Fri, 09 December 2016 02:41] by Moderator

Report message to a moderator

Re: create trigger error in SQL [message #658314 is a reply to message #658313] Fri, 09 December 2016 02:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8805
Registered: November 2002
Location: California, USA
Senior Member
Try running your trigger creation code from SQL*Plus, instead of SQL*Developer, and posting the results here, complete with line numbers and error message. You may get a more meaningful error message and the line numbers should enable us to see where line 34 is. It might be necessary to post create table statements for all of the tables involved for complete reproduction of the problem.
Re: create trigger error in SQL [message #658315 is a reply to message #658314] Fri, 09 December 2016 03:00 Go to previous message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also post the result of:
select * from v$version;
show user

Before, Please read How to use [code] tags and make your code easier to read.

Previous Topic: ORA-30006 Resource Busy, acquired with Wait timeout expired
Next Topic: Long data type in oracle
Goto Forum:
  


Current Time: Sun Feb 25 09:16:58 CST 2018

Total time taken to generate the page: 0.04406 seconds