c_bpartner_location_trg2.sql

Java写的ERP系统

CREATE OR REPLACE TRIGGER C_BPartner_Location_Trg2
AFTER INSERT OR UPDATE OF Updated
	ON C_BPartner_Location
/*************************************************************************
 * 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-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: C_BPartner_Location_Trg2.sql,v 1.2 2002/10/02 04:26:50 jjanke Exp $
 ***
 * Title:	Update Name of BP Locations with same City
 * Description:
 *	Set Name to City, Address1 
 *		Implementation not ideal, but prevents mutating trigger
 ************************************************************************/
BEGIN
--	DBMS_OUTPUT.PUT_LINE('C_BPartner_Location_Trg2');
	UPDATE 	C_BPartner_Location bpl
	  SET 	Name = 
	  		(SELECT City || ', ' || Address1
			FROM C_Location l 
			WHERE bpl.C_Location_ID=l.C_Location_ID)
	WHERE	Updated > SysDate - 1/1440		--	last minute
	  AND EXISTS 
	(	--	Locations of business partners with duplicate cities
	  	SELECT *
		FROM C_BPartner_Location bpl1
		  INNER JOIN C_Location l1 ON (bpl1.C_Location_ID=l1.C_Location_ID)
		WHERE --bpl.C_BPartner_Location_ID=bpl1.C_BPartner_Location_ID AND
		  (bpl1.C_BPartner_ID, l1.City) IN
		  	(	--	business partners with duplicate cities
				SELECT bpl2.C_BPartner_ID, l2.City
				FROM C_BPartner_Location bpl2
				  INNER JOIN C_Location l2 ON (bpl2.C_Location_ID=l2.C_Location_ID)
				GROUP BY bpl2.C_BPartner_ID, l2.City
				HAVING COUNT(*) > 1
			)
	);
END C_BPartner_Location_Trg2;
/