ad_menu_trg.sql

Java写的ERP系统

CREATE OR REPLACE TRIGGER AD_Menu_Trg
AFTER INSERT OR UPDATE OF Name, Description, IsActive
	ON AD_Menu
FOR EACH ROW
/******************************************************************************
 * ** Compiere Product **             Copyright (c) 1999-2001 Accorto, Inc. USA
 * Open  Source  Software        Provided "AS IS" without warranty or liability
 * When you use any parts (changed or unchanged), add  "Powered by Compiere" to
 * your product name;  See license details http://www.compiere.org/license.html
 ******************************************************************************
 */
DECLARE
    xTree_ID    NUMBER;
    xParent_ID  NUMBER;
    NextNo      NUMBER;
BEGIN

	--	Insert AD_Menu Trigger 
	--		for Translation 
	--		and TreeNode
	IF INSERTING THEN
    	--  Create Translation Row
	    INSERT INTO AD_Menu_Trl
    	    (AD_Menu_ID, AD_Language, AD_Client_ID, AD_Org_ID,
        	IsActive, Created, CreatedBy, Updated, UpdatedBy,
	        Name, Description, IsTranslated)
    	SELECT :new.AD_Menu_ID, AD_Language, :new.AD_Client_ID, :new.AD_Org_ID,
        	:new.IsActive, :new.Created, :new.CreatedBy, :new.Updated, :new.UpdatedBy,
	        :new.Name, :new.Description, 'N'
    	FROM    AD_Language
	   	WHERE	IsActive = 'Y' AND IsSystemLanguage = 'Y';

	    --  Create TreeNode --
    	--  get AD_Tree_ID + ParentID
	    SELECT  c.AD_Tree_Menu_ID, n.Node_ID 
		  INTO 	xTree_ID, xParent_ID
    	FROM    AD_ClientInfo c, AD_TreeNode n -- AD_TreeNodeMM n
	    WHERE   c.AD_Tree_Menu_ID=n.AD_Tree_ID
    	    AND n.Parent_ID IS NULL
        	AND c.AD_Client_ID=:new.AD_Client_ID;
    
    	--  DBMS_OUTPUT.PUT_LINE('Tree='||xTree_ID||'  Node='||:new.AD_Menu_ID||'  Parent='||xParent_ID);
    
	    --  Insert into TreeNode
    	INSERT INTO AD_TreeNode -- AD_TreeNodeMM
        	(AD_Client_ID, AD_Org_ID,
	        IsActive, Created, CreatedBy, Updated, UpdatedBy,
    	    AD_Tree_ID, Node_ID, 
        	Parent_ID, SeqNo)
	    VALUES
    	    (:new.AD_Client_ID, :new.AD_Org_ID,
        	:new.IsActive, :new.Created, :new.CreatedBy, :new.Updated, :new.UpdatedBy,
	        xTree_ID, :new.AD_Menu_ID, 
    	    xParent_ID, DECODE(:new.IsSummary, 'Y', 100, 999));     -- Summary Nodes first
	END IF;	--	Inserting

	--	AD_Ref_List update trigger
	--		synchronize name,... 
	IF UPDATING THEN
		IF (:old.IsActive != :new.IsActive) THEN
	    	--  get AD_Tree_ID + ParentID
		    SELECT  c.AD_Tree_Menu_ID, n.Node_ID 
			  INTO 	xTree_ID, xParent_ID
	    	FROM    AD_ClientInfo c, AD_TreeNode n --	AD_TreeNodeMM n
		    WHERE   c.AD_Tree_Menu_ID=n.AD_Tree_ID
    		    AND n.Parent_ID IS NULL
        		AND c.AD_Client_ID=:new.AD_Client_ID;
			--	Update
			UPDATE	AD_TreeNode	--	AD_TreeNodeMM
			  SET	IsActive = :new.IsActive
		   	WHERE	AD_Tree_ID = xTree_ID
			  AND	Node_ID=:new.AD_Menu_ID;
		ELSE
			UPDATE	AD_Menu_Trl
			SET		IsTranslated = 'N',
					Updated=SysDate
			WHERE	AD_Menu_ID = :new.AD_Menu_ID;
		END IF;
	END IF;	--	Updating

EXCEPTION
	WHEN NO_DATA_FOUND THEN
		RAISE_APPLICATION_ERROR (-20001, 'AD_Menu InsertTrigger Error: No ClientInfo or parent TreeNode');

END AD_Menu_Trg;
/