ad_role_orgaccess_trg.sql

Java写的ERP系统

CREATE OR REPLACE TRIGGER AD_Role_OrgAccess_Trg
AFTER INSERT OR UPDATE OR DELETE
	ON AD_Role_OrgAccess
 -- FOR EACH ROW
DECLARE
/*************************************************************************
 * The contents of this file are subject to the Compiere License.  You may
 * obtain a copy of the License at    http://www.compiere.org/license.html
 * Software is on an  "AS IS" basis,  WITHOUT WARRANTY OF ANY KIND, either
 * express or implied. See the License for details. Code: Compiere ERP+CRM
 * Copyright (C) 1999-2003 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: AD_Role_OrgAccess_Trg.sql,v 1.3 2003/05/04 06:46:07 jjanke Exp $
 ***
 * Title:	Update AD_Role.OrgList / ClientList
 *  for all Roles as otherwise mutating trigger
 * Description:
 ************************************************************************/
	CURSOR	CUR_Role	IS
		SELECT	*
		FROM		AD_Role
		FOR UPDATE;
	CURSOR	Cur_Org (role_ID NUMBER)	IS
		SELECT	AD_Client_ID, AD_Org_ID 
		FROM		AD_Role_OrgAccess
		WHERE	AD_Role_ID = role_ID
		  AND	IsActive='Y'
		ORDER BY	AD_Client_ID, AD_Org_ID;
	--
	v_ClientList		VARCHAR2(2000) := '';
	v_OrgList			VARCHAR2(2000) := '';
	v_Client_ID		NUMBER := -1;
BEGIN
	--	For each Role
	FOR r IN CUR_Role LOOP
		v_ClientList := '';
		v_OrgList := '';
		v_Client_ID := -1;
		--	Assemble Client/OrgList
		FOR c IN Cur_Org (r.AD_Role_ID) LOOP
			IF (v_Client_ID <> c.AD_Client_ID) THEN
				v_Client_ID := c.AD_Client_ID;
				IF (LENGTH(v_ClientList) <> 0) THEN
					v_ClientList := v_ClientList || ',';
				END IF;
				v_ClientList := v_ClientList || c.AD_Client_ID;
			END IF;
			--	Org
			IF (LENGTH(v_OrgList) <> 0) THEN
				v_OrgList := v_OrgList || ',';
			END IF;
			v_OrgList := v_OrgList || c.AD_Org_ID;
		END LOOP;	-- Org
		--
		IF (v_ClientList IS NULL OR LENGTH(v_ClientList) = 0) THEN
			v_ClientList := ' ';
		END IF;
		IF (v_OrgList IS NULL OR LENGTH(v_OrgList) = 0) THEN
			v_OrgList := ' ';
		END IF;
		
		DBMS_OUTPUT.PUT_LINE(r.Name 
			|| ': Client=' || r.ClientList || '->' || v_ClientList
			|| ' - Org= ' || r.OrgList || '->' || v_OrgList);
		--	Update Role
		UPDATE	AD_Role
		  SET	ClientList = v_ClientList,
				OrgList = v_OrgList
		WHERE CURRENT OF CUR_Role;
	END LOOP;	--	Role
	
END AD_Role_OrgAccess_Trg;
/