写这样一段代码，你需要多久？

[bigboss]

需求都定好，表也是定好的
其实就是下面的触发器要写
你需要多久？



create table GPS_LOCATION (
LOCA_ID NUMBER(12) not null,
LOCA_LOTY_ID NUMBER(12) not null,
LOCA_BUTY_ID NUMBER(12) not null,
LOCA_CODE VARCHAR2(10) not null,
LOCA_NAME VARCHAR2(30),
LOCA_DISTRICT NUMBER(12),
LOCA_CLASS NUMBER(12) not null,
LOCA_ANALYSIS NUMBER(12) not null,
LOCA_NUM_OF_TILLS SMALLINT,
LOCA_ADDRESS_ALIAS VARCHAR2(7),
LOCA_START_DATE DATE,
LOCA_CLOSE_DATE DATE,
LOCA_SHOP_MGR NUMBER(12),
LOCA_AREA_MGR NUMBER(12),
LOCA_DISP_SEQ SMALLINT,
LOCA_ADDRESS1 VARCHAR2(80),
LOCA_ADDRESS2 VARCHAR2(80),
LOCA_ADDRESS3 VARCHAR2(80),
LOCA_ADDRESS4 VARCHAR2(80),
LOCA_PHONE VARCHAR2(18),
LOCA_FAX VARCHAR2(18),
LOCA_EMAIL VARCHAR2(50),
LOCA_SIZE_SQ_FT NUMBER(16),
LOCA_MTH_RENT_OH NUMBER(16,6),
LOCA_VIP_FLAG NUMBER(1) not null,
LOCA_PM_FLAG NUMBER(1) not null,
LOCA_REPL_FLAG NUMBER(1) not null,
LOCA_SMQTY_FLAG NUMBER(1) not null,
LOCA_ALLOW_CASH_REFUND NUMBER(1),
LOCA_LANG_ID NUMBER(12),
LOCA_FUNC_LIST VARCHAR2(1024),
LOCA_ALLOW_FUNC VARCHAR2(1024),
LOCA_STOREROOM NUMBER(1),
LOCA_AUTO_CONFIRM NUMBER(1),
LOCA_COMP_ID NUMBER(12),
LOCA_MINCOMMR NUMBER(3),
LOCA_PAGE_BREAK NUMBER(1),
LOCA_QTY_OVERRIDE NUMBER(1),
LOCA_BRND VARCHAR2(100),
LOCA_BULK_PURCHASE NUMBER(1),
LOCA_OFFLINE_STATUS NUMBER(1),
LOCA_CREATE_DATE DATE,
LOCA_CREATE_BY NUMBER(12),
LOCA_MODIFY_DATE DATE,
LOCA_MODIFY_BY NUMBER(12),
LOCA_DELETE_FLAG NUMBER(1),
LOCA_DELETE_DATE DATE,
LOCA_DELETE_BY NUMBER(12),
constraint PK_GPS_LOCATION primary key (LOCA_ID)
)
/

create table GPS_BRANDS (
BRND_ID NUMBER(12) not null,
BRND_CHIN_ID NUMBER(12),
BRND_CODE VARCHAR2(3),
BRND_NAME VARCHAR2(30),
BRND_DESC VARCHAR2(20),
BRND_LANG_ID NUMBER(12),
BRND_COMP_ID NUMBER(12),
BRND_GENDER VARCHAR2(10),
BRND_BRND_GROUP NUMBER(12),
BRND_FRAN_COMM INTEGER,
BRND_RPT_SEQ NUMBER(12),
BRND_CREATE_DATE DATE,
BRND_CREATE_BY NUMBER(12),
BRND_MODIFY_DATE DATE,
BRND_MODIFY_BY NUMBER(12),
BRND_DELETE_FLAG NUMBER(1),
BRND_DELETE_DATE DATE,
BRND_DELETE_BY NUMBER(12),
constraint PK_GPS_BRANDS primary key (BRND_ID)
)
/

create table GPS_LOCATION_BRAND (
LOBR_ID NUMBER(12) not null,
LOBR_LOCA_ID NUMBER(12),
LOBR_BRND_ID NUMBER(12),
LOBR_COMP_ID NUMBER(12),
LOBR_CREATE_DATE DATE,
LOBR_CREATE_BY NUMBER(12),
LOBR_MODIFY_DATE DATE,
LOBR_MODIFY_BY NUMBER(12),
LOBR_DELETE_FLAG NUMBER(1),
LOBR_DELETE_DATE DATE,
LOBR_DELETE_BY NUMBER(12),
constraint PK_GPS_LOCATION_BRAND primary key (LOBR_ID)
)
/

/* Fully define procedures specified in package. */
PROCEDURE apply_transactions IS
/* Apply pending transactions in transactions table
to accounts table. Use cursor to fetch rows. */
CURSOR trans_cursor IS
SELECT acct_id, kind, amount FROM transactions
WHERE status = 'Pending'
ORDER BY time_tag
FOR UPDATE OF status; -- to lock rows
BEGIN
FOR trans IN trans_cursor LOOP
IF trans.kind = 'D' THEN
debit_account(trans.acct_id, trans.amount);
ELSIF trans.kind = 'C' THEN
credit_account(trans.acct_id, trans.amount);
ELSE
new_status := 'Rejected';
END IF;
UPDATE transactions SET status = new_status
WHERE CURRENT OF trans_cursor;
END LOOP;
END apply_transactions;


insert into gps_location_brand(lobr_id, lobr_loca_id, lobr_brnd_id, lobr_comp_id,
lobr_delete_flag) values(1, 1, 10, 1, 0);
insert into gps_location_brand(lobr_id, lobr_loca_id, lobr_brnd_id, lobr_comp_id,
lobr_delete_flag) values(2, 1, 20, 1, 0);

insert into gps_location_brand(lobr_id, lobr_loca_id, lobr_brnd_id, lobr_comp_id,
lobr_delete_flag) values(3, 1, 10, 1, 0);

insert into gps_brands(brnd_id, brnd_code) values(10, '010');
insert into gps_brands(brnd_id, brnd_code) values(20, '020');

insert into GPS_LOCATION (
LOCA_ID ,
LOCA_LOTY_ID ,
LOCA_BUTY_ID ,
LOCA_CODE ,
LOCA_CLASS ,
LOCA_ANALYSIS ,
LOCA_VIP_FLAG ,
LOCA_PM_FLAG ,
LOCA_REPL_FLAG,
LOCA_SMQTY_FLAG
) values(1, 1, 1, '10', 1, 1, 1,1,1,1)
/

SELECT 'GM+GL+'||brnd_code FROM gps_brands
WHERE BRND_ID = 10;
SELECT DISTINCT lobr_brnd_id FROM gps_location_brand
WHERE lobr_loca_id = 1
AND lobr_comp_id = 1
AND lobr_delete_flag = 0;

UPDATE gps_location
SET loca_brnd = (SELECT 'GM+GL+'||brnd_code FROM gps_brands
WHERE BRND_ID = 10)
WHERE loca_id = 1;
select loca_brnd from gps_location;

CREATE or REPLACE TRIGGER update_gps_location_trigger
BEFORE
INSERT OR UPDATE OR DELETE
ON GPS_LOCATION_BRAND
FOR EACH ROW
DECLARE
CURSOR lobr_brnd_id_cursor IS
SELECT DISTINCT lobr_brnd_id FROM gps_location_brand
WHERE lobr_loca_id = :new.lobr_loca_id
AND lobr_comp_id = :new.lobr_comp_id
AND lobr_delete_flag = 0;
BEGIN
FOR brnd IN lobr_brnd_id_cursor LOOP
UPDATE gps_location
SET loca_brnd = (SELECT 'GM+GL+'||brnd_code FROM gps_brands
WHERE BRND_ID = brnd.lobr_brnd_id)
WHERE loca_id = :new.lobr_loca_id;
END LOOP;
END update_gps_location_trigger;