Могу ли я увидеть DML внутри триггера Oracle?
Можно ли увидеть выполняемый DML (оператор SQL), который вызвал запуск триггера?
Например, внутри триггера INSERT я хотел бы получить это:
msgstr "вставить в myTable (имя) значения ('Фред')"
Я читал об ora_sql_txt(sql_text) в статьях, подобных этой, но не смог заставить его работать - не уверен, что это даже ведет меня по правильному пути?
Мы используем Oracle 10.
Заранее спасибо.
=========================
[ИЗМЕНЕНО] БОЛЬШЕ ПОДРОБНОСТИ: Нам необходимо скопировать существующую базу данных (DB1) в секретную базу данных (DB2), которая недоступна через сеть. Мне нужно синхронизировать эти базы данных. Это односторонняя синхронизация из (DB1) в (DB2), поскольку (DB2) будет содержать дополнительные таблицы и данные, которых нет в системе (DB1).
Я должен определить способ синхронизации этих баз данных, не отключая их (скажем, для резервного копирования и восстановления), потому что он должен оставаться в живых. Поэтому я подумал, что если я смогу сохранить действующий DML (когда данные меняются), я мог бы "воспроизвести" DML в новой базе данных, чтобы обновить его, как будто кто-то вручную вводил его обратно.
Я не могу перенести все данные из-за огромного размера, и я не могу просто скопировать измененные записи из-за ограничений FK и порядка, в котором я вставляю / обновляю записи. Я подумал, что, если бы я мог "воспроизвести" журнал событий, используя точный SQL, который изменил мастер, я мог бы синхронизировать базы данных.
Мой текущий план атаки состоял в том, чтобы вести журнал всех записей, которые были изменены, вставлены и удалены, и когда я хочу синхронизировать, система генерирует DML для вставки / обновления / удаления этих записей. Затем я просто беру файл.SQL в секретную систему и запускаю скрипт. Проблема, с которой я сталкиваюсь - это ФК. (Потому что, когда я генерирую DML, я знаю только, каково текущее состояние данных, а не путь к нему - так что упорядочение операторов является проблемой). Я думаю, я мог бы отключить все FK, выполнить слияние, а затем снова включить все FK...
Итак - мой подход хранения фактического DML как есть - отстой pondwater, или есть лучшее решение???
4 ответа
Эта функция работает только для триггеров "события", как описано здесь. Вы должны посмотреть на детальный аудит как механизм для этого. Подробности здесь
У меня была ситуация, когда мне нужно было перенести метаданные / изменения конфигурации (хранящиеся в нескольких таблицах) из среды разработки в производственную среду после тестирования. Что-то вроде Goldengate - это продукт, который нужно использовать для этого, но его установка и администрирование могут быть дорогостоящими и сложными.
Следующая процедура генерирует триггер и присоединяет его к таблице, для которой требуется сохранить DML. Триггер воссоздает DML и в следующем случае сохраняет его в таблице аудита - вам решать, что вы с ним делаете. Вы можете использовать операторы, сохраненные в таблице аудита, для воспроизведения изменений с заданного момента времени (вырезать и вставлять или разработать процедуру для их применения к цели).
Надеюсь, вы найдете это полезным.
procedure gen_trigger( p_tname in varchar2 )
is
l_theCursor integer default dbms_sql.open_cursor;
l_query varchar2(1000) default 'select * from ' || p_tname;
l_colCnt number := 0;
l_descTbl dbms_sql.desc_tab;
trg varchar(32767) := null;
expr varchar(32767) := null;
cmd varchar(32767) := null;
begin
dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );
dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
trg := q'#
create or replace trigger <%TABLE_NAME%>_audit
after insert or update or delete on <%TABLE_NAME%> for each row
declare
qs varchar2(20) := q'[q'^]';
qe varchar2(20) := q'[^']';
command clob;
nlsd varchar2(100);
begin
select value into nlsd from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';
execute immediate 'alter session set nls_date_format = ''YYYY/MM/DD hh24:mi:ss'' ';
if inserting then
command := <%INSERT_COMMAND%>;
end if;
if updating then
command := <%UPDATE_COMMAND%>;
end if;
if deleting then
command := <%DELETE_COMMAND%>;
end if;
insert into x_audit values (systimestamp, command);
execute immediate q'+alter session set nls_date_format = '+'|| nlsd || q'+'+';
end;
#';
-- Create the insert command
cmd := q'#'insert into <%TABLE_NAME%> (<%INSERT_COLS%>) values ('||<%INSERT_VAL%>||')'#';
-- columns clause
for i in 1 .. l_colCnt loop
if expr is not null then
expr := expr || ',';
end if;
expr := expr || l_descTbl(i).col_name;
end loop;
cmd := replace(cmd,'<%INSERT_COLS%>',expr);
-- values clause
expr := null;
for i in 1 .. l_colCnt loop
if expr is not null then
expr := expr || q'#||','||#';
end if;
expr := expr || 'qs||:new.' || l_descTbl(i).col_name || '||qe';
end loop;
cmd := replace(cmd,'<%INSERT_VAL%>',expr);
trg := replace(trg,'<%INSERT_COMMAND%>',cmd);
-- create the update command
-- set clause
expr := null;
cmd := q'#'update <%TABLE_NAME%> set '||<%UPDATE_COLS%>||' where '||<%WHERE_CLAUSE%>#';
for i in 1 .. l_colCnt loop
if expr is not null then
expr := expr || q'#||','||#';
end if;
expr := expr || q'#'#' || l_descTbl(i).col_name || q'# = '||#'|| 'qs||:new.'||l_descTbl(i).col_name || '||qe';
end loop;
null;
cmd := replace(cmd,'<%UPDATE_COLS%>',expr);
trg := replace(trg,'<%UPDATE_COMMAND%>',cmd);
-- create the delete command
expr := null;
cmd := q'#'delete <%TABLE_NAME%> where '||<%WHERE_CLAUSE%>#';
trg := replace(trg,'<%DELETE_COMMAND%>',cmd);
-- where clause using primary key columns (used by update and delete)
expr := null;
for pk in (SELECT column_name FROM all_cons_columns WHERE constraint_name = (
SELECT constraint_name FROM user_constraints
WHERE UPPER(table_name) = UPPER(p_tname) AND CONSTRAINT_TYPE = 'P'
)) loop
if expr is not null then
expr := expr || q'#|| ' and '||#';
end if;
expr := expr || q'#'#' || pk.column_name || q'# = '||#'|| 'qs||:old.'|| pk.column_name || '||qe';
end loop;
if expr is null then -- must have a primary key
raise_application_error(-20000,'The table must have a primary key defined');
end if;
trg := replace(trg,'<%WHERE_CLAUSE%>',expr);
trg := replace(trg,'<%TABLE_NAME%>',p_tname);
execute immediate trg;
null;
exception
when others then
execute immediate 'alter session set nls_date_format=''YYYY/MM/DD'' ';
raise;
end;
/* Example
create table t1 (
col1 varchar2(100),
col2 number,
col3 date,
constraint pk_t1 primary key (col1)
)
/
BEGIN
GEN_TRIGGER('T1');
END;
/
-- Trigger generated ....
create or replace trigger t1_audit after
insert or
update or
delete on t1 for each row
declare
qs varchar2(20) := q'[q'^]';
qe varchar2(20) := q'[^']';
command clob;
nlsd varchar2(100);
begin
select value into nlsd from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';
execute immediate 'alter session set nls_date_format = ''YYYY/MM/DD hh24:mi:ss'' ';
if inserting then
command := 'insert into T1 (COL1,COL2,COL3) values ('||qs||:new.col1||qe||','||qs||:new.col2||qe||','||qs||:new.col3||qe||')';
end if;
if updating then
command := 'update T1 set '||'COL1 = '||qs||:new.col1||qe||','||'COL2 = '||qs||:new.col2||qe||','||'COL3 = '||qs||:new.col3||qe||' where '||'COL1 = '||qs||:old.col1||qe;
end if;
if deleting then
command := 'delete T1 where '||'COL1 = '||qs||:old.col1||qe;
end if;
insert into x_audit values
(systimestamp, command
);
execute immediate q'+alter session set nls_date_format = '+'|| nlsd || q'+'+';
end;
*/
"Мой подход к хранению фактического DML, как он есть, сосет прудуотер?" Да..
Строгого порядка DML на вашем DB1 на самом деле не существует. Многократные процессы, многоядерные ядра, вещи, по существу, происходят в одно и то же время.
И DML, даже когда это происходит последовательно, не действует как это. Скажем, следующие два оператора обновления выполняются в отдельных процессах с отдельными транзакциями, где обновление в транзакции 2 начинается до фиксации транзакции 1:
update table_a set col_a = 10 where col_b = 'A' -- transaction 1 update table_a set col_c = 'Error' where col_a = 10 -- transaction 2
Поскольку изменения, сделанные в первой транзакции, не видны для второй транзакции, строки, измененные второй транзакцией, не будут включать в себя строки первой. Но если вам удастся захватить DML и воспроизвести его последовательно, изменения транзакции 1 будут видны, поэтому изменения транзакции 2 будут другими. (См. Стр. 40 и 41 из Тома Кайта, Эксперт Oracle Database Architecture, второе издание.)
Надеюсь, вы используете переменные связывания, поэтому сам DML не будет иметь смысла:
update table_a set col_a = :col_a where id = :idЧто теперь? Итак, вы хотите DML с привязками к нему.Вы используете последовательности? Если это так, next_val не будет синхронизирован между DB1 и DB2. (Например, сбои экземпляра могут привести к потере значений, произойдет ли сбой обеих систем одновременно?) И если вы имеете дело с RAC, где значение next_val меняется в зависимости от узла, забудьте об этом.
Я бы начал с изучения репликации Oracle.
Когда запускается код триггера, разве вы уже не знаете dml, который вызвал его запуск?
CREATE OR REPLACE TRIGGER Print_salary_changes
BEFORE INSERT OR UPDATE ON Emp_tab
FOR EACH ROW
...
В этом случае это должен быть оператор вставки или обновления в таблице emp_tab.
Чтобы узнать, было ли это обновление или вставка
if inserting then
...
elsif updating then
...
end if;
Точные значения столбцов доступны в псевдо-столбцах: old и: new.