语法错误,期待的是BEGIN CASE DECLARE END ENDIF IDENTIFIER IF LOOP这几个关键字,但是得到的是其他的
create or replace trigger trig_trackerinfo after insert or update or delete on test_tracker for each row declare tid number(10); begin select count(c.test_id) into tid from test_central c where c.test_id = :new.test_id , c.test_name = :new.test_name and c.test_department = :new.test_department if inserting then if (tid <1) then insert into test_central(test_id,test_name,test_department, test_inserttime,state,operater) values(:new.test_id,:new.test_name,:new.test_department,sysdate, 'insert',user); else update test_central set test_inserttime = sysdate,state='insert', operater = user where test_id =:new.test_id,test_name=:new.test.name, test_department = new.test_department;else if updating then if (tid <1) then insert into test_central(test_id,test_name,test_department, test_inserttime,state,operater) values(:new.test_id,:new.test_name,:new.test_department,sysdate, 'update',user); else update test_central set test_inserttime = sysdate,state='update', operater = user where test_id =:new.test_id,test_name=:new.test.name, test_department = new.test_department; else if deleting then if (tid <1) then insert into test_central(test_id,test_name,test_department, test_inserttime,state,operater) values(:new.test_id,:new.test_name,:new.test_department,sysdate, 'delete',user); else update test_central set test_inserttime = sysdate,state='delete', operater = user where test_id =:new.test_id,test_name=:new.test.name, test_department = new.test_department; end if; exception when others then dbms_output.put_line('happened exception...'); end;不知道哪里有语法错误 望指教!
就是有两个表A表中有:ID NAME AGE B表中有:ID NAME AGE INSERTTIME UPDATETIME DELETETIME STATE USER 当你在A表中执行动作的时候,在B表中根据ID判断是否存在如果存在则修改,反之插入。 也就是说在A表中执行: insert INTO A VALUES(‘111’,‘11’,‘11’); B表中就显示: ID NAME AGE INSERTTIME UPDATETIME DELETETIME STATE USER 111 11 11 sysdate insert user 如果执行更新或者删除的话,判断ID是否存在 存在直接把UPDATETIME或者DELETETIME加上否则另起一行插入。。 大致就是这样。我那个是写的很乱 第一次写请多多包涵。。再次谢谢各位的回复
create or replace trigger trr before update or delete or insert on TEST_TRACKER for each row declare c number; begin select count(*) into c from TEST_CENTRAL where test_id = :new.test_id; if c = 0 then if inserting then insert into TEST_CENTRAL values (:new.test_id, :new.test_name, :new.test_department, sysdate, 'insert', user); elsif updating then insert into TEST_CENTRAL values (:new.test_id, :new.test_name, :new.test_department, sysdate, 'update', user); elsif deleting then insert into TEST_CENTRAL values (:new.test_id, :new.test_name, :new.test_department, sysdate, 'delete', user); end if; else if inserting then update TEST_CENTRAL set test_name = :new.test_name, test_department = :new.test_department, test_inserttime = sysdate, state = 'insert', operater = user where test_id = :new.test_id; elsif deleting then update TEST_CENTRAL set test_name = :new.test_name, test_department = :new.test_department, test_inserttime = sysdate, state = 'delete', operater = user where test_id = :new.test_id; elsif updating then update TEST_CENTRAL set test_name = :new.test_name, test_department = :new.test_department, test_inserttime = sysdate, state = 'update', operater = user where test_id = :new.test_id; end if; end if; end;
elsif deleting then update TEST_CENTRAL set test_name = :new.test_name, test_department = :new.test_department, test_inserttime = sysdate, state = 'delete', operater = user where test_id = :new.test_id;DELETING OLD
改成old create or replace trigger trr before update or delete or insert on TEST_TRACKER for each row declare c number; begin select count(*) into c from TEST_CENTRAL where test_id = :new.test_id; if c = 0 then if inserting then insert into TEST_CENTRAL values (:new.test_id, :new.test_name, :new.test_department, sysdate, 'insert', user); elsif updating then insert into TEST_CENTRAL values (:new.test_id, :new.test_name, :new.test_department, sysdate, 'update', user); elsif deleting then insert into TEST_CENTRAL values (:old.test_id, :old.test_name, :old.test_department, sysdate, 'delete', user); end if; else if inserting then update TEST_CENTRAL set test_name = :old.test_name, test_department = :old.test_department, test_inserttime = sysdate, state = 'insert', operater = user where test_id = :old.test_id; elsif deleting then update TEST_CENTRAL set test_name = :new.test_name, test_department = :new.test_department, test_inserttime = sysdate, state = 'delete', operater = user where test_id = :new.test_id; elsif updating then update TEST_CENTRAL set test_name = :new.test_name, test_department = :new.test_department, test_inserttime = sysdate, state = 'update', operater = user where test_id = :new.test_id; end if; end if; end;
上面有个地方改错了改到inserting那了。把deleting new后面的都改成old
create or replace trigger trr before update or delete or insert on TEST_TRACKER for each row declare c number; begin select count(*) into c from TEST_CENTRAL where test_id = :new.test_id; if c = 0 then if inserting then insert into TEST_CENTRAL values (:new.test_id, :new.test_name, :new.test_department, sysdate, 'insert', user); elsif updating then insert into TEST_CENTRAL values (:new.test_id, :new.test_name, :new.test_department, sysdate, 'update', user); elsif deleting then update TEST_CENTRAL set test_name = :old.test_name, test_department = :old.test_department, test_inserttime = sysdate, state = 'delete', operater = user where test_id = :old.test_id; end if; else if inserting then update TEST_CENTRAL set test_name = :new.test_name, test_department = :new.test_department, test_inserttime = sysdate, state = 'insert', operater = user where test_id = :new.test_id; elsif deleting then
update TEST_CENTRAL set test_name = :old.test_name, test_department = :old.test_department, test_inserttime = sysdate, state = 'delete', operater = user where test_id = :old.test_id;
elsif updating then update TEST_CENTRAL set test_name = :new.test_name, test_department = :new.test_department, test_inserttime = sysdate, state = 'update', operater = user where test_id = :new.test_id; end if; end if;end;
after insert or update or delete on test_tracker
for each row
declare
tid number(10);
begin
select count(c.test_id) into tid from test_central c
where c.test_id = :new.test_id
, c.test_name = :new.test_name
and c.test_department = :new.test_department
if inserting then
if (tid <1) then
insert into test_central(test_id,test_name,test_department,
test_inserttime,state,operater)
values(:new.test_id,:new.test_name,:new.test_department,sysdate,
'insert',user);
else
update test_central set test_inserttime = sysdate,state='insert',
operater = user
where test_id =:new.test_id,test_name=:new.test.name,
test_department = new.test_department;else if updating then
if (tid <1) then
insert into test_central(test_id,test_name,test_department,
test_inserttime,state,operater)
values(:new.test_id,:new.test_name,:new.test_department,sysdate,
'update',user);
else
update test_central set test_inserttime = sysdate,state='update',
operater = user
where test_id =:new.test_id,test_name=:new.test.name,
test_department = new.test_department;
else if deleting then
if (tid <1) then
insert into test_central(test_id,test_name,test_department,
test_inserttime,state,operater)
values(:new.test_id,:new.test_name,:new.test_department,sysdate,
'delete',user);
else
update test_central set test_inserttime = sysdate,state='delete',
operater = user
where test_id =:new.test_id,test_name=:new.test.name,
test_department = new.test_department;
end if;
exception
when others then
dbms_output.put_line('happened exception...');
end;不知道哪里有语法错误 望指教!
B表中有:ID NAME AGE INSERTTIME
UPDATETIME
DELETETIME
STATE
USER
当你在A表中执行动作的时候,在B表中根据ID判断是否存在如果存在则修改,反之插入。
也就是说在A表中执行:
insert INTO A
VALUES(‘111’,‘11’,‘11’);
B表中就显示:
ID NAME AGE INSERTTIME UPDATETIME DELETETIME STATE USER
111 11 11 sysdate insert user
如果执行更新或者删除的话,判断ID是否存在 存在直接把UPDATETIME或者DELETETIME加上否则另起一行插入。。
大致就是这样。我那个是写的很乱 第一次写请多多包涵。。再次谢谢各位的回复
before update or delete or insert on TEST_TRACKER
for each row
declare
c number;
begin
select count(*) into c from TEST_CENTRAL where test_id = :new.test_id;
if c = 0 then
if inserting then
insert into TEST_CENTRAL
values
(:new.test_id,
:new.test_name,
:new.test_department,
sysdate,
'insert',
user);
elsif updating then
insert into TEST_CENTRAL
values
(:new.test_id,
:new.test_name,
:new.test_department,
sysdate,
'update',
user);
elsif deleting then
insert into TEST_CENTRAL
values
(:new.test_id,
:new.test_name,
:new.test_department,
sysdate,
'delete',
user);
end if;
else
if inserting then
update TEST_CENTRAL
set test_name = :new.test_name,
test_department = :new.test_department,
test_inserttime = sysdate,
state = 'insert',
operater = user
where test_id = :new.test_id;
elsif deleting then
update TEST_CENTRAL
set test_name = :new.test_name,
test_department = :new.test_department,
test_inserttime = sysdate,
state = 'delete',
operater = user
where test_id = :new.test_id;
elsif updating then
update TEST_CENTRAL
set test_name = :new.test_name,
test_department = :new.test_department,
test_inserttime = sysdate,
state = 'update',
operater = user
where test_id = :new.test_id;
end if;
end if;
end;
update TEST_CENTRAL
set test_name = :new.test_name,
test_department = :new.test_department,
test_inserttime = sysdate,
state = 'delete',
operater = user
where test_id = :new.test_id;DELETING OLD
create or replace trigger trr
before update or delete or insert on TEST_TRACKER
for each row
declare
c number;
begin
select count(*) into c from TEST_CENTRAL where test_id = :new.test_id;
if c = 0 then
if inserting then
insert into TEST_CENTRAL
values
(:new.test_id,
:new.test_name,
:new.test_department,
sysdate,
'insert',
user);
elsif updating then
insert into TEST_CENTRAL
values
(:new.test_id,
:new.test_name,
:new.test_department,
sysdate,
'update',
user);
elsif deleting then
insert into TEST_CENTRAL
values
(:old.test_id,
:old.test_name,
:old.test_department,
sysdate,
'delete',
user);
end if;
else
if inserting then
update TEST_CENTRAL
set test_name = :old.test_name,
test_department = :old.test_department,
test_inserttime = sysdate,
state = 'insert',
operater = user
where test_id = :old.test_id;
elsif deleting then
update TEST_CENTRAL
set test_name = :new.test_name,
test_department = :new.test_department,
test_inserttime = sysdate,
state = 'delete',
operater = user
where test_id = :new.test_id;
elsif updating then
update TEST_CENTRAL
set test_name = :new.test_name,
test_department = :new.test_department,
test_inserttime = sysdate,
state = 'update',
operater = user
where test_id = :new.test_id;
end if;
end if;
end;
new后面的都改成old
before update or delete or insert on TEST_TRACKER
for each row
declare
c number;
begin
select count(*) into c from TEST_CENTRAL where test_id = :new.test_id;
if c = 0 then
if inserting then
insert into TEST_CENTRAL
values
(:new.test_id,
:new.test_name,
:new.test_department,
sysdate,
'insert',
user);
elsif updating then
insert into TEST_CENTRAL
values
(:new.test_id,
:new.test_name,
:new.test_department,
sysdate,
'update',
user);
elsif deleting then
update TEST_CENTRAL
set test_name = :old.test_name,
test_department = :old.test_department,
test_inserttime = sysdate,
state = 'delete',
operater = user
where test_id = :old.test_id;
end if;
else
if inserting then
update TEST_CENTRAL
set test_name = :new.test_name,
test_department = :new.test_department,
test_inserttime = sysdate,
state = 'insert',
operater = user
where test_id = :new.test_id;
elsif deleting then
update TEST_CENTRAL
set test_name = :old.test_name,
test_department = :old.test_department,
test_inserttime = sysdate,
state = 'delete',
operater = user
where test_id = :old.test_id;
elsif updating then
update TEST_CENTRAL
set test_name = :new.test_name,
test_department = :new.test_department,
test_inserttime = sysdate,
state = 'update',
operater = user
where test_id = :new.test_id;
end if;
end if;end;