关于一个Oracle触发器的问题请教大家了，不胜感谢

[Apple]

请大家看看我如下的这个触发器有木有什么问题，问题出在哪里？该怎么修正？

先谢谢大家指教了！

create or replace trigger Tig_CNOOC_ZJDC_MAXIMO_ONOFF
after insert or update on CNOOC_ZJDC_MAXIMO_ONOFF
for each row

declare _assetNo varchar2(100);

declare _dateTime date;

declare _tagName varchar2(400);

declare _dataValue number(15,3);

declare _type varchar2(10);

declare _tableName varchar2(50);

declare _tableColumn varchar2(50);

declare _sql varchar2(2000);

if inserting or updating then
   _tagName:=:new.TAGID;
   _dataValue:=:new.DATAVALUE;
   _type:=:new.TYPE;
   _assetNo:=:new.EQNUM;
   _dateTime:=:new.DATETIME;
   
   declare _cnt number;
   _sql:='select count(TagName) into '||_cnt||',TagName into '||_tableName||',TableName into '||_tableColumn||' from DC_RALATIONSHIP_TAG where TagName='||_tagName||' and Type='||_type||'';
   execute immediate _sql;
   
   if _cnt>0 then  
      declare _num number;
      _sql:='select count(*) into '||_num||' from '||_tableName||' where EQNUM='||_assetNo||' and DATETIME=todate('||_dateTime||','yyyy:MM:dd hh24:mi:ss')';
      execute immediate _sql;
      if _num=0 then       
         _sql:='insert into _tableName(EQNUM,DATETIME,'||_tableColumn||') values('||_assetNo||',todate('||_dateTime||','yyyy:MM:dd hh24:mi:ss'),'||_dataValue||')';
         execute immediate _sql;
      else      
         _sql:='update '||_tableName||' set '||_tableColumn||'='||_dataValue||' where EQNUM='||_assetNo||' and DATETIME=todate('||_dateTime||','yyyy:MM:dd hh24:mi:ss')';
         execute immediate _sql;
      end if
   end if
   
end if

[Apple]

好了，修正完毕，感谢楼上两位

[007-x]

lz的数据库是Oracle吗,是的话语法差太远了

[cosio]

出什么问题了!

[Apple]

是Oracle，正因为不熟悉语法，所以才有错误，望不吝指教，Oracle10g

[cosio]

[Quote=引用 1 楼 tx2730 的回复:]
lz的数据库是Oracle吗,是的话语法差太远了
[/Quote]

差太远了?什么意思,不懂?

[007-x]

[Quote=引用 4 楼 cosio 的回复:]
引用 1 楼 tx2730 的回复:
lz的数据库是Oracle吗,是的话语法差太远了


差太远了?什么意思,不懂?
[/Quote]
呵,我是说语法错太多了

[Apple]

ORA-04098:触发器‘CNOOC_ZJDC.TIG_CNOOC_ZJDC_MAXIMO_ONOFF’无效且未通过重新验证

[cosio]

create or replace trigger Tig_CNOOC_ZJDC_MAXIMO_ONOFF
  after update,insert on CNOOC_ZJDC_MAXIMO_ONOFF  
  for each row
declare
  -- local variables here
begin
   if inserting or updating then
     --event
   end if; 

end Tig_CNOOC_ZJDC_MAXIMO_ONOFF;

[Apple]

[Quote=引用 5 楼 tx2730 的回复:]

引用 4 楼 cosio 的回复:
引用 1 楼 tx2730 的回复:
lz的数据库是Oracle吗,是的话语法差太远了


差太远了?什么意思,不懂?

呵,我是说语法错太多了
[/Quote]


可以贴一个类似的例子否，谢谢了

[cosio]

    CREATE OR REPLACE TRIGGER trg_del_emp_info 
    BEFORE DELETE 
    ON emp 
    FOR EACH ROW 
    DECLARE 
    -- local variables here 
    BEGIN 
    INSERT INTO emp3(autoid,deptno,empno,ename,del_rq) 
    VALUES(seq_trg_del_autoid.NEXTVAL,:OLD.deptno,:OLD.empno,:OLD.ename,sysdate); 
    END trg_del_emp_info; 

[007-x]

按照你的逻辑,帮你改了下,试试看行不行

CREATE OR REPLACE TRIGGER tig_cnooc_zjdc_maximo_onoff
   AFTER INSERT OR UPDATE
   ON cnooc_zjdc_maximo_onoff
   FOR EACH ROW
DECLARE
   v_assetno       VARCHAR2 (100);
   v_datetime      DATE;
   v_tagname       VARCHAR2 (400);
   v_datavalue     NUMBER (15, 3);
   v_type          VARCHAR2 (10);
   v_tablename     VARCHAR2 (50);
   v_tablecolumn   VARCHAR2 (50);
   v_num           NUMBER;
   v_sql           VARCHAR2 (2000);
   v_cnt           NUMBER;
BEGIN
   v_tagname := :NEW.tagid;
   v_datavalue := :NEW.datavalue;
   v_type := :NEW.TYPE;
   v_assetno := :NEW.eqnum;
   v_datetime := :NEW.datetime;
   SELECT COUNT (tagname), tagname, tablename
     INTO v_cnt, v_tablename, v_tablecolumn
     FROM dc_ralationship_tag
    WHERE tagname = v_tagname AND TYPE = v_type;
   IF v_cnt > 0
   THEN
      v_sql :=
            'select count(*)  from '
         || v_tablename
         || ' where EQNUM=:1 and DATETIME=:2';
      EXECUTE IMMEDIATE v_sql
                   INTO v_num
                  USING v_assetno, v_datetime;
      IF v_num = 0
      THEN
         v_sql :=
               'insert into '
            || v_tablename
            || '(EQNUM,DATETIME,'
            || v_tablecolumn
            || ') values(:1,:2,:3)';
         EXECUTE IMMEDIATE v_sql
                     USING v_assetno, v_datetime, v_datavalue;
      ELSE
         v_sql :=
               'update '
            || v_tablename
            || ' set '
            || v_tablecolumn
            || '=:1 where EQNUM=:2 and DATETIME=:3';
         EXECUTE IMMEDIATE v_sql
                     USING v_datavalue, v_assetno, v_datetime;
      END IF;
   END IF;
END;

[Apple]

[Quote=引用 9 楼 cosio 的回复:]
SQL code
CREATE OR REPLACE TRIGGER trg_del_emp_info
BEFORE DELETE
ON emp
FOR EACH ROW
DECLARE
-- local variables here
BEGIN
INSERT INTO emp3(autoid,deptn……
[/Quote]

_sql:='select count(*) into '||_num||' from '||_tableName||' where EQNUM='||_assetNo||' and DATETIME=todate('||_dateTime||','yyyy:MM:dd hh24:mi:ss')';
execute immediate _sql;


你好，请问我这样拼出一条SQL然后执行，可不可这样写？应该怎么实现呐？

[Apple]

好的，谢谢楼上，我试试

[cosio]

create or replace trigger Tig_CNOOC_ZJDC_MAXIMO_ONOFF
after insert or update on CNOOC_ZJDC_MAXIMO_ONOFF
for each row
declare 
 _cnt INT;
 _num INT;

if inserting or updating then
   SELECT COUNT(1) INTO _cnt from DC_RALATIONSHIP_TAG where TagName=:new.TAGID and Type=:new.TYPE;  
   if _cnt>0 then  
      select count(1) into  _num  where EQNUM= :new.EQNUM and DATETIME=:new.DATETIME;
      if _num=0 then       
         insert into _tableName(EQNUM,DATETIME) 
         values(:new.EQNUM,:new.DATETIME);
      else      
         update tab1 set col1=:new.DATAVALUE where EQNUM=:new.EQNUM and DATETIME=:new.DATETIME;
      end IF;
   end IF;
   
end IF;
END;