March 1, 2017 at 9:04 pm
Comments posted to this topic are about the item Finding Triggers
March 1, 2017 at 10:11 pm
Nice, easy question today, thanks Steve
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
March 1, 2017 at 11:56 pm
Nice question Steve. I'm not too familiar with OBJECTPROPERTY, but this got me Reading the documentation. Thanks!
But (there's always one), I answered that this can't be done with OBJECTPROPERTY and I think that should be the correct answer. One can define Instead Of Triggers on a table and the count for these can't be found using OBJECTPROPERTY. But I answered before reading the documentation for OBJECTPROPERTY and honestly had no idea there was a TableTriggertypeTriggerCount-property at all, so I still think the question was good. Thanks again!
Sql Server blog: http://www.tsql.nu
March 2, 2017 at 1:20 am
Magnus Ahlkvist - Wednesday, March 1, 2017 11:56 PMOne can define Instead Of Triggers on a table and the count for these can't be found using OBJECTPROPERTY.
That's true!
March 2, 2017 at 2:43 am
Not used Instead of Triggers but from the little I have read it looks like these replace the normal Insert, Update, Delete triggers. If so would these be included in the various Trigger Counts for ObjectProperty?
March 2, 2017 at 6:29 am
I got it wrong, but I learned something new. I usually use sys.triggers.
March 2, 2017 at 6:41 am
The only problem is if you have triggers that affect multiple types. Here's an example for a trigger that would be count 3 times.
CREATE TABLE TriggerTest(
ColA int
)
GO
SELECT 'Before Trigger Creation',
OBJECTPROPERTY( OBJECT_ID('TriggerTest'), 'TableDeleteTriggerCount'),
OBJECTPROPERTY( OBJECT_ID('TriggerTest'), 'TableInsertTriggerCount'),
OBJECTPROPERTY( OBJECT_ID('TriggerTest'), 'TableUpdateTriggerCount')
GO
CREATE TRIGGER TX_TriggerTest ON TriggerTest
FOR INSERT,DELETE,UPDATE
AS
PRINT 'Something'
GO
SELECT 'After Trigger Creation',
OBJECTPROPERTY( OBJECT_ID('TriggerTest'), 'TableDeleteTriggerCount'),
OBJECTPROPERTY( OBJECT_ID('TriggerTest'), 'TableInsertTriggerCount'),
OBJECTPROPERTY( OBJECT_ID('TriggerTest'), 'TableUpdateTriggerCount')
GO
DROP TABLE TriggerTest
March 2, 2017 at 6:44 am
I got the answer right but now know that that was wrong and the real answer is that it cannot be done. I have a table that has two triggers on it. One trigger writes a row to a history table and the other does maintenance on another table depending on what was done to the original table. According to a query of the object properties, I have two triggers that insert, two that update and two that delete for an apparent total of six. The whole is not actually the sum of the parts in this case. So while both triggers do each of those things, there is no way to tell if those operations are single function or multifunction triggers. I don't see a way to tell from the OBJECTPROPERTY how many distinct items it is counting.
March 2, 2017 at 8:52 am
Interesting question, thanks Steve, to exercise logical reasoning...😉
That a table has INSTEAD OF Trigger can be determined using OBJECTPROPERTY function with Property name 'HasInsteadOfTrigger',
e.g. in the example posted above by Luis Cazares would be as follows: OBJECTPROPERTY( OBJECT_ID('TriggerTest'), 'HasInsteadOfTrigger') as [HasInsteadOfTrigger];
A comprehensive article, not only on this topic is in Simple Talk here.
March 2, 2017 at 9:16 am
George Vobr - Thursday, March 2, 2017 8:52 AMInteresting question, thanks Steve, to exercise logical reasoning...😉
That a table has INSTEAD OF Trigger can be determined using OBJECTPROPERTY function with Property name 'HasInsteadOfTrigger',
e.g. in the example posted above by Luis Cazares would be as follows:OBJECTPROPERTY( OBJECT_ID('TriggerTest'), 'HasInsteadOfTrigger') as [HasInsteadOfTrigger];
A comprehensive article, not only on this topic is in Simple Talk here.
The question is about the number of trigger!
determine the number of triggers a table has
March 2, 2017 at 9:21 am
The best method is the following:SELECT COUNT(*) FROM sys.triggers
WHERE parent_id = OBJECT_ID('TriggerTest')
March 2, 2017 at 10:16 am
Hadn't considered INSTEAD OF triggers. Good catch. I'll alter the question.
March 2, 2017 at 11:17 am
I like the question. It made me think of the need to break down the types of triggers when looking at a new system and evaluating the state of the system. I had been just lumping all of the triggers per table into one lump sum.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 7, 2017 at 7:00 am
What am I misunderstanding when I read this (https://msdn.microsoft.com/en-us/library/ms176105.aspx)
Returns information about schema-scoped objects in the current database. For a list of schema-scoped objects, see sys.objects (Transact-SQL). This function cannot be used for objects that are not schema-scoped, such as data definition language (DDL) triggers and event notifications.
March 7, 2017 at 10:56 am
Budd - Tuesday, March 7, 2017 7:00 AMWhat am I misunderstanding when I read this (https://msdn.microsoft.com/en-us/library/ms176105.aspx)Returns information about schema-scoped objects in the current database. For a list of schema-scoped objects, see sys.objects (Transact-SQL). This function cannot be used for objects that are not schema-scoped, such as data definition language (DDL) triggers and event notifications.
What's your question? This wasn't about DDL triggers.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply