c_bpartner_location_trg.sql

来自「Java写的ERP系统」· SQL 代码 · 共 53 行

CREATE OR REPLACE TRIGGER C_BPartner_Location_Trg
BEFORE INSERT OR UPDATE OF C_Location_ID, Updated
	ON C_BPartner_Location
FOR EACH ROW
/*************************************************************************
 * 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_Trg.sql,v 1.3 2002/10/01 02:23:31 jjanke Exp $
 ***
 * Title:	Update Name with (updated) Location details
 * Description:
 *	Set Name to City 
 *		or if not exists to Address1 
 *		or if not exists to "-'
 ************************************************************************/
DECLARE
	v_City				VARCHAR2(60);
	v_Address1			VARCHAR2(60);
	v_Address2			VARCHAR2(60);
BEGIN
	DBMS_OUTPUT.PUT_LINE('C_BPartner_Location_Trg');

	--	Get Address Info
	SELECT	City, Address1, Address2 
	  INTO	v_City, v_Address1, v_Address2 
	FROM 	C_Location 
	WHERE 	C_Location_ID=:new.C_Location_ID;
	
	--	No City
	IF (v_City IS NULL) THEN
		v_City := v_Address1;
	END IF;

	--	No City nor Address 1
	IF (v_City IS NULL) THEN
		v_City := '-';
		DBMS_OUTPUT.PUT_LINE('No Address Info ID=' || :new.C_BPartner_Location_ID);
	END IF;

	--	Set Name
	:new.Name := v_City;

EXCEPTION
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('Not unique - ' || SQLERRM);
		:new.Name := SUBSTR(v_City || ', ' || v_Address1 || ', ' || v_Address2, 1, 60);
END C_BPartner_Location_Trg;
/