如何在Sybase ASE 16.0中查找触发器以及在表上定义的架构？ [英] How to find triggers along with schemas defined on a table in Sybase ASE 16.0?
问题描述
我试图在Sybase ASE 16.0中找到在给定架构中的表上定义的所有触发器，并且可以在与给定表不同的架构中定义触发器（用户具有所有必需的权限）。例如，下面的表将在dbo模式（默认）中创建，触发器分别在dbo和s1模式中创建。
创建表tblAllTypesTriggers （
Id int NOT NULL主键，
Name varchar（30），
Salary int，
Gender varchar（10），
DepartmentId int
）
锁定所有页
/
创建触发器tblAllTypesTriggers_6
on tblAllTypesTriggers
进行插入
AS
开始
-做
结束
/
创建触发器s1.tblAllTypesTriggers_6
on tblAllTypesTriggers
插入
AS
开始
-执行
END
/
有没有办法获取此表上定义的触发器详细信息（名称和架构）？
我尝试了以下操作：
从sysobjects so1中选择so2.name，so2.uid，sysobjects so2其中
（so2.id = so1.del trig或so2.id = so1.instrig或so2.id = so1.updtrig或
so2.id = so1.seltrig）和so1.name ='tblAllTypesTriggers'
- 问题：每种触发器类型仅给出1个触发器（1个插入，1个删除，1个更新触发器）
sp_helptrigger'tblAllTypesTriggers'
- 问题：仅给出在表上定义的触发器名称，而不给出其模式
sp_depends'tblAllTypesTriggers'
- 问题：还列出了引用该表的其他表上的触发器
sysobjects。{instrig / deltrig / updtrig} 列可以回溯过去的美好时光，当时一个表最多只能包含1个每种类型的触发器。 [是的，这些列仍在使用，但仅用于表所有者创建的第一个触发器；或在而不是触发器的情况下，视图的所有者。]
请记住，对于 sysobjects .type ='TR'条目中， deltrig 列包含触发器所属的基本表的ID ...来自 sysobjects.deltrig列说明：
$ b pre>
deltrig：如果条目是表，则删除触发器的存储过程ID。如果条目是触发器，则表ID为
不幸的是，在其他触发器中，它变得更加复杂（例如，在这种情况下，由非表所有者创建）还会将关联的行添加到 sysconstraints （ sysconstraints.constrid = object_id（> trigger_name<）），使用 sysconstraints.status 列（位图）指定触发器是否用于插入，更新和/或删除。
使用示例代码（将 s1 替换为 markp ），这应该使您对自己的想法有所了解反对：
选择id，
剩下的（name，30）为objname，
类型，
（user_name（uid），10）作为'所有者'，
deltrig，
instrig，
updrig
来自sysobjects
，其中名称类似于'tblAll％ '
按类型排序，uid
go
id objname typ e所有者deltrig instrig updrig
----------- ------------------------------ ---- ---------- ----------- ----------- -----------
752002679 tblAllTypesTriggers_6 TR dbo 736002622 0 0
816002907 tblAllTypesTriggers_6 TR markp 736002622 0 0
736002622 tblAllTypesTriggers U dbo 0 752002679 0
-这里我们看到2x触发器（type = ）具有deltrig = 736002622 =表的ID（类型= U）
select * from sysconstraints其中tableid = object_id（'tblAllTypesTriggers'）
go
colid constrid tableid错误状态spare2
------ ----------- ----------- --------- ------------ -----------
0 816002907 736002622 0 1024 0
-在这里我们看到了markp的触发器（ constrid = 816002907）与
-dbo的表格（tableid = 736002622），状态为& 1024 = 1024
-表示这是一个'插入'触发器
注意：您可以从 sp_helptrigger 的源代码中得出上述所有内容。 （ Duh，Mark！？）[是的，默认的 sp_helptrigger 可以从一些编辑中受益，例如，显示每个触发器的所有者/架构。]
一个快速而无用的查询来回答您的问题：
选择左（o1.name，30）作为标签名，选择
左（user_name（o1.uid），10）作为标签所有者，选择
左（o2.name，30）作为trigname，选择
（user_name（o2.uid），10）作为sysobjects o1中的Trigowner
，
sysobjects o2
，其中o1.name ='tblAllTypesTriggers'
和o1.type ='U'
和o2.deltrig = o1.id
和o2.type ='TR'
依次乘以1,2,4,3
去
tabname tabowner trigname trigowner
------------------------------ ----------- ---------------------------- ----------
tblAllTypesTriggers dbo tblAllTypesTriggers_6 dbo
tblAllTypesTriggers dbo tblAl lTypesTriggers_6 markp
在 sysobjects ， sysconstraints 和 sp_helptrigger 的来源，您应该能够根据需要对数据进行切片。
I am trying to find all the triggers defined on a table in a given schema in Sybase ASE 16.0, and the triggers can be defined in different schema than the given table (User has all required permissions). Example, below table will be created in dbo schema (default) and the triggers in dbo and s1 schema respectively.
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
/
Is there any way to get both the trigger details (name and schema) that are defined on this table?
I have tried following:
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'
- Issue: only gives 1 trigger per trigger type (1 insert, 1 delete, 1 update trigger)
sp_helptrigger 'tblAllTypesTriggers'
- Issue: only gives trigger names defined on table, but not their schema
sp_depends 'tblAllTypesTriggers'
- Issue: also lists triggers on other tables that refer this table
The sysobjects.{instrig/deltrig/updtrig} columns are a throwback to the good ol' days when a table could only have at most 1 of each type of trigger. [Yes, these columns are still used but only for the first trigger created by the table's owner; or the view's owner in the case of instead of triggers.]
Keep in mind that for sysobjects.type='TR' entries, the deltrig column contains the id of the base table to which the trigger belongs ... from the 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
Unfortunately, it gets a bit more complicated in that additional triggers (eg, created by the non-table owner in this case) also get an associated row added to sysconstraints (sysconstraints.constrid = object_id(>trigger_name<)), with the sysconstraints.status column (a bitmap) designating whether the trigger is for insert, update and/or delete.
Using your example code (and replacing s1 with markp), this should give you an idea of what you're up against:
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
NOTE: You can derive all of the above from the source code for sp_helptrigger. ("Duh, Mark!" ?) [And yes, the default sp_helptrigger could benefit from some edits, eg, showing the owner/schema of each trigger.]
A quick, off-the-top-of-my-head query to answer your question:
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
Between sysobjects, sysconstraints and the source for sp_helptrigger you should be able to slice-n-dice the data as you wish eh.
这篇关于如何在Sybase ASE 16.0中查找触发器以及在表上定义的架构？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
