database triggers - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

database triggers

database triggers# Database - 数据库

b*e2000-06-12 07:06

1 楼

here's a problem i have.. a stupid software vendor P stores
their date format in unix epoch (seconds from 1/1/1970) and
all other applications we have can only handle standard
oracle dates (MM/DD/YYYY). so in order to make them talk to
each other i have to add a column (oracle_date) to the table
used by software P. and every time that software does an
insert or update i want to make sure my additional column
gets updated as well..
so i wanted to use a database trigger on this table..
CREATE TRIG

s*l2000-06-12 07:06

2 楼

Use INSTEAD OF Trigger and try it. Then I think it will be
OK.

【在 b****e 的大作中提到】

: here's a problem i have.. a stupid software vendor P stores
: their date format in unix epoch (seconds from 1/1/1970) and
: all other applications we have can only handle standard
: oracle dates (MM/DD/YYYY). so in order to make them talk to
: each other i have to add a column (oracle_date) to the table
: used by software P. and every time that software does an
: insert or update i want to make sure my additional column
: gets updated as well..
: so i wanted to use a database trigger on this table..
: CREATE TRIG

b*e2000-06-12 07:06

3 楼

i changed BEFORE to instead of.. still same error :(

【在 s***l 的大作中提到】

:
: Use INSTEAD OF Trigger and try it. Then I think it will be
: OK.

s*l2000-06-12 07:06

4 楼

CREATE TRIGGER tt_trigger AFTER INSERT OR UPDATE OF
unix_date
ON tt_test
FOR EACH ROW
BEGIN
update tt_test set oracle_date =
unix_to_ora(:new.unix_date);
END;
Maybe (update tt_test set oracle_date =
unix_to_ora(:new.unix_date);) is enough.
See if this works. The original trigger will cause infinite
trigger events in theory, so maybe that is why it cause
error.
This one will not cause infinite trigger events.
And also, BEFORE is surely not what you want. The PL/SQL
statements

【在 b****e 的大作中提到】

: i changed BEFORE to instead of.. still same error :(

s*k2000-06-12 07:06

5 楼

I think whatever you change before into after or instead of,
you will
get an ora--mutating table error for trigger should refer to
the same
row that you just fired trigger. If you really need using
trigger to
work around your problem, why not create a table to store
the primary
key in your table of P and oracle date corresponding to the
unix time?
You can populate it by create ... select.... into...

【在 s***l 的大作中提到】

: CREATE TRIGGER tt_trigger AFTER INSERT OR UPDATE OF
: unix_date
: ON tt_test
: FOR EACH ROW
: BEGIN
: update tt_test set oracle_date =
: unix_to_ora(:new.unix_date);
: END;
: Maybe (update tt_test set oracle_date =
: unix_to_ora(:new.unix_date);) is enough.

b*e2000-06-12 07:06

6 楼

i was hoping to avoid that extra table and extra join in my
program..
but i guess until oracle comes out with column-level-trigger
that's
the only thing i can do now..
whoever works for oracle.. consider this an enhancement
request :)

【在 s*k 的大作中提到】

: I think whatever you change before into after or instead of,
: you will
: get an ora--mutating table error for trigger should refer to
: the same
: row that you just fired trigger. If you really need using
: trigger to
: work around your problem, why not create a table to store
: the primary
: key in your table of P and oracle date corresponding to the
: unix time?