关于ddl触发器，在匹配触发条件的时候总是报错，有没有大佬可以帮忙解答一下呢，求教

Question

数据库是oracle，配置一个ddl触发器，针对direct用户，一旦有删表的操作，如果是匹配到我规定的20170901的表就记录下来
create table ddl_log(create_time date,op_table varchar2(128),op_user varchar2(30),op_type varchar2(64),is_send number default '0');--新建一张记录事件的表。
CREATE OR REPLACE TRIGGER DROP_DDL_TRIGGER AFTER DROP
ON DIRECT.SCHEMA
declare
col number;
begin
select count(*) into col from dba_objects WHERE owner='DIRECT' AND LAST_DDL_TIME=SYSDATE and OBJECT_NAME='test_20170901';
if col>0 then
insert into ddl_log(create_time,op_table,op_type) values((select sysdate from dual),'test_20170901','drop');
END IF;
END;
然后ddl触发器报错在select那一行说表或视图不存在（其实dba_objects这个表是肯定存在的），接着我有用另一种方法写触发器。
CREATE OR REPLACE TRIGGER DROP_DDL_TRIGGER AFTER DROP
ON DIRECT.SCHEMA
begin
if EXISTS(SELECT 1 FROM dba_objects WHERE owner='DIRECT' AND LAST_DDL_TIME=(SELECT SYSDATE FROM DUAL) AND OBJECT_NAME='test_20170901') THEN
insert into ddl_log(create_time,op_table,op_type) values(sysdate,'test_20170901','drop');
END IF;
END;
然后又报错说exists只能由于sql语句，请问怎么解决！求教

Answer 1

if里面不能用exists，试一试这样：

CREATE OR REPLACE TRIGGER DROP_DDL_TRIGGER
AFTER DROP ON DIRECT.SCHEMA
DECLARE
    col NUMBER;
BEGIN
    SELECT COUNT(*)
    INTO col
    FROM dba_objects
    WHERE owner='DIRECT'
    AND last_ddl_time=SYSDATE
    AND object_name='test_20170901';
    IF col > 0 THEN
        INSERT INTO ddl_log(create_time, op_table, op_type)
        VALUES(SYSDATE, 'test_20170901', 'drop');
    END IF;
END;