| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Triggers, duh! :(
I', playing around with an ORACLE7.3.4. I want to check if some attributes changed after an UPDATE. The thing I want is an event-log.
The trigger:
CREATE OR REPLACE TRIGGER start_3.update_stores
AFTER UPDATE ON start_3.stores FOR EACH ROW
BEGIN
IF TO_CHAR(:new.date_signed) != TO_CHAR(:old.date_signed) THEN
INSERT INTO start_3.adminlog
(admin_user,action_id,action_table,target,action_description) VALUES
(:new.admin_user,2,'STORES',:old.foo_store_id,'Butiken påskriven av ' ||
:new.admin_user);
END IF;
IF TO_CHAR(:new.closing_date) != TO_CHAR(:old.closing_date) THEN
IF TO_CHAR(:new.closing_date) = '' THEN
INSERT INTO start_3.adminlog
(admin_user,action_id,action_table,target,action_description) VALUES
(:new.admin_user,23,'STORES',:old.foo_store_id,'Datum för stängning togs
bort av ' || :new.admin_user);
ELSE
INSERT INTO start_3.adminlog
(admin_user,action_id,action_table,target,action_description) VALUES
(:new.admin_user,22,'STORES',:old.foo_store_id,'Datum för stängning
sattes till ' || :new.closing_date || ' av ' || :new.admin_user);
END IF;
END IF;
END;
The table:
SQL> desc stores;
Name Null? Type ------------------------------- -------- ---- DATE_CREATION NOT NULL DATE DATE_UPGRADE DATE DATE_SIGNED DATE DOMAIN VARCHAR2(255) DOMESTIC NOT NULL NUMBER(1) INTERNAL NOT NULL NUMBER(1) LICENCE_REMINDER_SENT NOT NULL NUMBER(1) LITE_REMOVE_NOTE_SENT NOT NULL NUMBER(1) MANAGER_REMOVE_NOTE_SENT NOT NULL NUMBER(1) LOCKED NOT NULL NUMBER(1) STORE_STATUS NOT NULL VARCHAR2(20) FOO_STORE_ID NOT NULL VARCHAR2(10) UPGRADE_ADV_SENT NOT NULL NUMBER(1) STORE_DELETED NOT NULL NUMBER(1) HTML_DIRECTORY VARCHAR2(100) DATA_DIRECTORY VARCHAR2(100) STORE_FRONT_URL VARCHAR2(100) ORDERED_PRODUCT_TYPE NOT NULL CHAR(3) CLOSING_DATE DATE ADMIN_USER VARCHAR2(40) ORG_CHECK NUMBER(1) MSG VARCHAR2(255) DATE_DELETED DATE
SQL> I meddle around a bit:
SQL> select '"' || to_char(date_signed) || '"' from stores;
'"'||TO_CHAR(DATE_SIGNED)||'"'
'"'||TO_CHAR(DATE_SIGNED)||'"'
22 rows selected.
SQL> I do some more testing since I by now am convinced that empty strings are what I get from to_char if no value has been assigned to the attribute:
SQL> select closing_date from stores where 2 to_char(closing_date) = '';
no rows selected
SQL> select closing_date from stores where 2 to_char(closing_date) != '';
no rows selected
SQL> select closing_date from stores where 2 to_char(closing_date) != to_char('');
no rows selected
SQL> This is where I get confused. The first part of the trigger, checking DATE_SIGNED, works perfectly in the development environment but NOT in the testenvironment. The second part, checking CLOSING_DATE, works in neither of them.
Could anyone give me some pointers as to WHY this, in my opinion, weird behaviour appears?
Thanks in advance
Peter
Received on Wed Jun 14 2000 - 00:00:00 CDT
 |
 |