How can i find invalid trigger on MSSQL ?

[steffenfritz]

Hallo,

i looking for something to find invalid trigger on my database. In Oracle i found something. There is a entry for status in user_objects.But i looking for MSSQL.
Have somebody a tip ?

sorry my english is not so good ;-)

[MAK]

can your explain me what you mean by invalid trigger?

[steffenfritz]

Invaled mean that the object can not used, because there is an mistake in syntax or one of the tables can not be found.I have spend a lot of time in google but i dont found somthing what can help me.

[skhanal]

There's no concept of INVALID in sql server. If there is an error while creating the trigger it won't be created at all.

[steffenfritz]

I have create an trigger for insert in one table and he insert something in a other table, but the other table is not on database created. The trigger is enabled when i look at him.

select a.name,OBJECTPROPERTY(OBJECT_ID(a.name), 'ExecIsTriggerDisabled') from dbo.sysobjects a
where a.XTYPE = 'TR'

There must be something to see the status from stored procedures.

[rmiao]

Yon can disable it with 'alter table'.

[steffenfritz]

That is not the problem, to disable a trigger, the problem is that the trigger will be created in dbo.SYSOBJECTS and the TRIGGEROBJECT is enabled but there are some mistakes at the craete-statement and i must found trigger with mistakes!!

i hope there is somebody, who knows something about that

[skhanal]

If the create statement is syntactically correct then the trigger is valid for sql server. It will bomb at run time if you refer to a non existing table.

Look for "Deferred Name Resolution and Compilation" in books online.