DatabaseProcApplicationCreatedLinks
sybsystemprocssp_helptrigger  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3     /*
4     ** 17186, "Usage: sp_helptrigger {table_name | view_name}"
5     ** 17460, "Object must be in the current database."
6     ** 17461, "Object does not exist in this database."
7     ** 17187, "Object '%1!' is not a table or a view"
8     ** 17188, "There are no %1! triggers declared on %2! %3!'
9     */
10    
11    /*
12    ** System stored proc that takes a table/view name in the current database
13    ** and lists all triggers associated with the table or view:
14    **	Instead of trigger (views only)
15    **	For (after) triggers (tables only)
16    ** Also lists
17    **	The action that fires the trigger (insert, update, delete, or
18    **	  a combination of these actions)
19    **	For for-triggers only, the trigger's ORDER number, if any
20    **	The enabled/disabled status of the trigger
21    */
22    
23    /*
24    ** IMPORTANT NOTE:
25    ** This stored procedure uses the built-in function object_id() in the
26    ** where clause of a select query. If you intend to change this query
27    ** or use the object_id() or db_id() builtin in this procedure, please read the
28    ** READ.ME file in the $DBMS/generic/sproc directory to ensure that the rules
29    ** pertaining to object-id's and db-id's outlined there, are followed.
30    */
31    create or replace procedure sp_helptrigger
32        @objname varchar(767) = NULL /* table to report triggers on */
33    AS
34    
35        declare @msg varchar(1024)
36        declare @trigname varchar(255)
37        declare @trigid int
38        declare @updtrig int
39        declare @deltrig int
40        declare @instrig int
41        declare @order_num int
42        declare @type char(2) /* Object type */
43        declare @obj_type varchar(5)
44        declare @trig_type varchar(10)
45        declare @stat int
46        declare @stat2 int
47    
48        if @@trancount = 0
49        begin
50            set chained off
51        end
52    
53        set transaction isolation level 1
54    
55        if @objname is null
56        begin
57            /* 17186, "Usage: sp_helptrigger {table_name | view_name}" */
58            raiserror 17186
59            return (1)
60        end
61    
62        /*
63        **  Check to see that the object names are local to the current database.
64        */
65        if @objname like "%.%.%" and
66            substring(@objname, 1, charindex(".", @objname) - 1) != db_name()
67        begin
68            /* 17390, "Table or view must be in the current database." */
69            raiserror 17390
70            return (1)
71        end
72    
73        /*
74        **  Check to see the the table/view exists 
75        */
76        select @type = type, /* Object type indicator */
77            @updtrig = updtrig,
78            @deltrig = deltrig,
79            @instrig = instrig,
80            @stat2 = sysstat2
81        from sysobjects
82        where id = object_id(@objname)
83    
84        /*
85        **  If object doesn't exist, return.
86        */
87        if (@type is null)
88        begin
89            /* 17492, "Table or view doesn't exist in current database." */
90            raiserror 17492, @objname
91            return (1)
92        end
93    
94        /*
95        ** If the object is not a table nor a view, return.
96        */
97        if (@type not in ('V', 'U'))
98        begin
99            /* 17187, "Object '%1!' is not a table or a view" */
100           raiserror 17187, @objname
101           return (1)
102       end
103   
104       if (@type = 'V')
105           select @obj_type = "view"
106       else
107           select @obj_type = "table"
108   
109       /*
110       ** Template for the table we will output.
111       **	    trigger_name 
112       **	    firing_action: action[s] that fire[s] the trigger
113       **	    firing_order:  ('for' triggers only) trigger's order number
114       **	    trigger_status: 'enabled/disabled' 
115       */
116       create table #sptriggertab(
117           trigger_name varchar(255),
118           firing_action char(22),
119           firing_order int,
120           trigger_status char(8)) lock allpages
121   
122       /*
123       **  Check instead of triggers on views
124       */
125       if (@type = 'V')
126       begin --{
127           if (@instrig = 0 and @updtrig = 0 and @deltrig = 0)
128           begin
129               begin
130                   /* 17188, "There are no %1! triggers declared on %2! %3!' */
131                   exec sp_getmessage 17188, @msg output
132                   print @msg, 'INSTEAD_OF', @obj_type, @objname
133                   return (0)
134               end
135           end
136   
137           /* Accumulate all instead of triggers in temp table */
138           if (@instrig <> 0)
139               insert #sptriggertab values
140               (
141                   object_name(@instrig),
142                   case when (@instrig = @updtrig and @updtrig = @deltrig)
143                       then 'insert, update, delete'
144                       when (@instrig = @updtrig)
145                       then 'insert, update'
146                       when (@instrig = @deltrig)
147                       then 'insert, delete'
148                       else
149                           'insert'
150                   end,
151                   0,
152                   /* sysstat2 O2_DISABLE_INSTRIG 0x100000 */
153                   case when @stat2 & 1048576 <> 0
154                       then 'disabled'
155                       else
156                           'enabled'
157                   end
158               )
159           if (@updtrig <> 0 and @updtrig <> @instrig)
160               insert #sptriggertab values
161               (
162                   object_name(@updtrig),
163                   case when (@updtrig = @deltrig)
164                       then 'update, delete'
165                       else
166                           'update'
167                   end,
168                   0,
169                   /* sysstat2 O2_DISABLE_UPDTRIG 0x400000 */
170                   case when @stat2 & 4194304 <> 0
171                       then 'disabled'
172                       else
173                           'enabled'
174                   end
175               )
176           if (@deltrig <> 0 and @deltrig <> @instrig and @deltrig <> @updtrig)
177               insert #sptriggertab values
178               (
179                   object_name(@deltrig),
180                   'delete',
181                   0,
182                   /* sysstat2 O2_DISABLE_DELTRIG 0x200000 */
183                   case when @stat2 & 2097152 <> 0
184                       then 'disabled'
185                       else
186                           'enabled'
187                   end
188               )
189       end --}
190       else -- type is table
191       begin --{
192           /* First look in sysconstraints for multiple triggers */
193           declare curs_constr
194           cursor for
195           select constrid, colid, status
196           from sysconstraints
197           where tableid = object_id(@objname)
198               and status & 3584 <> 0
199           for read only
200   
201           open curs_constr
202           fetch curs_constr
203           into @trigid, @order_num, @stat
204   
205           while (@@sqlstatus = 0)
206           begin
207               insert into #sptriggertab values
208               (
209                   object_name(@trigid),
210                   case when @stat & 3584 = 3584
211                       then 'insert, update, delete'
212                       when @stat & 3072 = 3072
213                       then 'insert, update'
214                       when @stat & 1536 = 1536
215                       then 'insert, delete'
216                       when @stat & 2560 = 2560
217                       then 'update, delete'
218                       when @stat & 512 = 512
219                       then 'delete'
220                       when @stat & 1024 = 1024
221                       then 'insert'
222                       when @stat & 2048 = 2048
223                       then 'update'
224                       else
225                           ''
226                   end,
227                   case when @order_num = 0
228                       /* Weight non-ordered trig so it shows last */
229                       then 10000
230                       else
231                           @order_num
232                   end,
233                   /* cnsstatus CON_TRIGDISABLE 0x1000 */
234                   case when @stat & 4096 <> 0
235                       then 'disabled'
236                       else
237                           'enabled'
238                   end
239               )
240               fetch curs_constr
241               into @trigid, @order_num, @stat
242   
243           end
244           close curs_constr
245   
246           /*
247           ** Now accumulate triggers stored in sysobjects 
248           */
249           if (@instrig <> 0)
250               insert #sptriggertab values
251               (
252                   object_name(@instrig),
253                   case when (@instrig = @updtrig and @updtrig = @deltrig)
254                       then 'insert, update, delete'
255                       when (@instrig = @updtrig)
256                       then 'insert, update'
257                       when (@instrig = @deltrig)
258                       then 'insert, delete'
259                       else
260                           'insert'
261                   end,
262                   10000,
263                   /* sysstat2 O2_DISABLE_INSTRIG 0x100000 */
264                   case when @stat2 & 1048576 <> 0
265                       then 'disabled'
266                       else
267                           'enabled'
268                   end
269               )
270           if (@updtrig <> 0 and @updtrig <> @instrig)
271               insert #sptriggertab values
272               (
273                   object_name(@updtrig),
274                   case when (@updtrig = @deltrig)
275                       then 'update, delete'
276                       else
277                           'update'
278                   end,
279                   10000,
280                   /* sysstat2 O2_DISABLE_UPDTRIG 0x400000 */
281                   case when @stat2 & 4194304 <> 0
282                       then 'disabled'
283                       else
284                           'enabled'
285                   end
286               )
287           if (@deltrig <> 0 and @deltrig <> @instrig and @deltrig <> @updtrig)
288               insert #sptriggertab values
289               (
290                   object_name(@deltrig),
291                   'delete',
292                   10000,
293                   /* sysstat2 O2_DISABLE_DELTRIG 0x200000 */
294                   case when @stat2 & 2097152 <> 0
295                       then 'disabled'
296                       else
297                           'enabled'
298                   end
299               )
300           /*
301           **	Check for no table triggers - #sptriggertab is empty
302           **	and sysobjects.{instrig,updtrig,deltrig} are not set.
303           */
304           if not exists (select * from #sptriggertab)
305               if (@instrig = 0 and @updtrig = 0 and @deltrig = 0)
306               begin
307                   /* 17188, "There are no %1! triggers declared on %2! %3!' */
308                   exec sp_getmessage 17188, @msg output
309                   print @msg, 'FOR', @obj_type, @objname
310                   return (0)
311               end
312       end --}
313   
314       /* Translate firing order of 10000 to "no order" */
315       select trigger_name,
316           firing_action,
317           case when firing_order = 0
318               /* 'N/A' applies to views */
319               then "N/A"
320               when firing_order < 10000
321               then convert(char(8), firing_order)
322               /* "no order" applies to tables */
323               else "no order"
324           end as firing_order_alpha,
325           firing_order,
326           trigger_status
327       into #sptriggertab_1
328       from #sptriggertab
329   
330       /* Prettyprint the results */
331       exec sp_autoformat #sptriggertab_1,
332           "'Trigger Name' = trigger_name, 'Firing Action' = firing_action,\
333   'Firing Order' = firing_order_alpha, 'Enabled Status' = trigger_status", "order by firing_order"
334   
335       return (0)
336   


exec sp_procxmode 'sp_helptrigger', 'AnyMode'
go

Grant Execute on sp_helptrigger to public
go
DEFECTS
 MEST 4 Empty String will be replaced by Single Space 225
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 331
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 82
 MGTP 3 Grant to public sybsystemprocs..sp_helptrigger  
 MGTP 3 Grant to public sybsystemprocs..sysconstraints  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNER 3 No Error Check should check return value of exec 131
 MNER 3 No Error Check should check @@error after insert 139
 MNER 3 No Error Check should check @@error after insert 160
 MNER 3 No Error Check should check @@error after insert 177
 MNER 3 No Error Check should check @@error after insert 207
 MNER 3 No Error Check should check @@error after insert 250
 MNER 3 No Error Check should check @@error after insert 271
 MNER 3 No Error Check should check @@error after insert 288
 MNER 3 No Error Check should check return value of exec 308
 MNER 3 No Error Check should check @@error after select into 315
 MNER 3 No Error Check should check return value of exec 331
 MUCO 3 Useless Code Useless Brackets 59
 MUCO 3 Useless Code Useless Brackets 70
 MUCO 3 Useless Code Useless Brackets 87
 MUCO 3 Useless Code Useless Brackets 91
 MUCO 3 Useless Code Useless Brackets 97
 MUCO 3 Useless Code Useless Brackets 101
 MUCO 3 Useless Code Useless Brackets 104
 MUCO 3 Useless Code Useless Brackets 125
 MUCO 3 Useless Code Useless Brackets 127
 MUCO 3 Useless Code Useless Brackets 133
 MUCO 3 Useless Code Useless Brackets 138
 MUCO 3 Useless Code Useless Brackets 142
 MUCO 3 Useless Code Useless Brackets 144
 MUCO 3 Useless Code Useless Brackets 146
 MUCO 3 Useless Code Useless Brackets 159
 MUCO 3 Useless Code Useless Brackets 163
 MUCO 3 Useless Code Useless Brackets 176
 MUCO 3 Useless Code Useless Brackets 205
 MUCO 3 Useless Code Useless Brackets 249
 MUCO 3 Useless Code Useless Brackets 253
 MUCO 3 Useless Code Useless Brackets 255
 MUCO 3 Useless Code Useless Brackets 257
 MUCO 3 Useless Code Useless Brackets 270
 MUCO 3 Useless Code Useless Brackets 274
 MUCO 3 Useless Code Useless Brackets 287
 MUCO 3 Useless Code Useless Brackets 305
 MUCO 3 Useless Code Useless Brackets 310
 MUCO 3 Useless Code Useless Brackets 335
 MUIN 3 Column created using implicit nullability 116
 QISO 3 Set isolation level 53
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconstraints.csysconstraints clustered
(tableid, colid)
Intersection: {tableid}
197
 VUNU 3 Variable is not used @trigname 36
 VUNU 3 Variable is not used @trig_type 44
 CRDO 2 Read Only Cursor Marker (has for read only clause) 195
 MTR1 2 Metrics: Comments Ratio Comments: 34% 31
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 25 = 30dec - 7exi + 2 31
 MTR3 2 Metrics: Query Complexity Complexity: 136 31

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysconstraints  
calls proc sybsystemprocs..sp_getmessage  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
   reads table sybsystemprocs..sysusermessages  
   reads table master..syslanguages (1)  
calls proc sybsystemprocs..sp_autoformat  
   writes table sybsystemprocs..sp_autoformat_rset_005 
   reads table tempdb..syscolumns (1)  
   writes table sybsystemprocs..sp_autoformat_rset_003 
   calls proc sybsystemprocs..sp_autoformat  
   writes table sybsystemprocs..sp_autoformat_rset_002 
   calls proc sybsystemprocs..sp_namecrack  
   reads table tempdb..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_001 
   reads table master..syscolumns (1)  
   reads table master..systypes (1)  
   read_writes table tempdb..#colinfo_af (1) 
   writes table sybsystemprocs..sp_autoformat_rset_004 
read_writes table tempdb..#sptriggertab (1) 
reads table sybsystemprocs..sysobjects  
writes table tempdb..#sptriggertab_1 (1)