代码之家 › 专栏 › 技术社区 › Saurabh Garg

如何在Sybase ASE 16.0中查找在表上定义的触发器和模式?

sap-ase sybase triggers database

Saurabh Garg · 技术社区 · 7 年前

我试图在Sybase ASE 16.0的给定模式中找到表上定义的所有触发器,并且可以在不同于给定表的模式中定义触发器(用户拥有所有必需的权限)。例如,下表将分别在dbo模式(默认)和dbo和s1模式中创建触发器。

CREATE TABLE tblAllTypesTriggers ( 
    "Id"            int NOT NULL primary key,
    "Name"          varchar(30),
    "Salary"        int,
    "Gender"        varchar(10),
    "DepartmentId"  int 
    )
LOCK ALLPAGES
/

CREATE TRIGGER tblAllTypesTriggers_6
ON tblAllTypesTriggers 
FOR INSERT 
AS 
BEGIN 
 -- do something
END
/

CREATE TRIGGER s1.tblAllTypesTriggers_6
ON tblAllTypesTriggers 
FOR INSERT 
AS 
BEGIN 
 -- do something
END
/

有什么方法可以同时获取该表中定义的触发器详细信息(名称和模式)吗?

我尝试了以下几点:

select so2.name, so2.uid from sysobjects so1, sysobjects so2 where
(so2.id = so1.deltrig or so2.id = so1.instrig or so2.id=so1.updtrig or
so2.id=so1.seltrig)  and so1.name= 'tblAllTypesTriggers'

sp_helptrigger 'tblAllTypesTriggers'

问题:只给出表上定义的触发器名称,但不给出其模式

sp_depends 'tblAllTypesTriggers'

1 回复 | 直到 7 年前

markp-fuso 7 年前

这个 sysobjects.{instrig/deltrig/updtrig} instead of

请记住条目 deltrig公司 sysobjects.deltrig column description :

deltrig: Stored procedure ID of a delete trigger if the entry is a table. Table ID if the entry is a trigger

系统约束 sysconstraints.constrid 对象id(>触发器名称<) ),使用 sysconstraints.status 指定触发器是否用于插入、更新和/或删除的列(位图)。

使用示例代码(并替换 s1 markp ),这应该会让你知道自己面对的是什么:

select  id,
        left(name,30) as objname,
        type,
        left(user_name(uid),10) as 'owner',
        deltrig,
        instrig,
        updtrig
from    sysobjects
where   name like 'tblAll%'
order by type,uid
go

 id          objname                        type owner      deltrig     instrig     updtrig
 ----------- ------------------------------ ---- ---------- ----------- ----------- -----------
   752002679 tblAllTypesTriggers_6          TR   dbo          736002622           0           0
   816002907 tblAllTypesTriggers_6          TR   markp        736002622           0           0
   736002622 tblAllTypesTriggers            U    dbo                  0   752002679           0

 -- here we see the 2x triggers (type = TR) have deltrig = 736002622 = id of the table (type = U)


select * from sysconstraints where tableid = object_id('tblAllTypesTriggers')
go

 colid  constrid    tableid     error       status      spare2
 ------ ----------- ----------- ----------- ----------- -----------
      0   816002907   736002622           0        1024           0

 -- here we see markp's trigger (constrid = 816002907) is associated with
 -- the dbo's table (tableid = 736002622), with status & 1024 = 1024 
 -- indicating that this is a 'insert' trigger

注意:您可以从 .(“嗯,马克!”?)[是的,默认值 sp\U帮助触发器 可以从一些编辑中受益,例如,显示每个触发器的所有者/模式。]

回答您的问题时,请快速回答我的问题:

select left(o1.name,30)           as tabname,
       left(user_name(o1.uid),10) as tabowner,
       left(o2.name,30)           as trigname,
       left(user_name(o2.uid),10) as trigowner
from   sysobjects o1,
       sysobjects o2
where  o1.name    = 'tblAllTypesTriggers'
and    o1.type    = 'U'
and    o2.deltrig = o1.id
and    o2.type    = 'TR'
order by 1,2,4,3
go

 tabname                        tabowner   trigname                       trigowner
 ------------------------------ ---------- ------------------------------ ----------
 tblAllTypesTriggers            dbo        tblAllTypesTriggers_6          dbo
 tblAllTypesTriggers            dbo        tblAllTypesTriggers_6          markp

系统对象 系统约束 以及