How to force immediate recompile of triggers and detect errors?

I need a way to programmatically (via JDBC) find out which triggers for a table may not compile properly, so that I can disable the bad triggers.

I can do this fine in Oracle but cannot figure out if there's a way to do this in SqlServer. (In Oracle I'd just "alter trigger... compile" and select from user_errors.)

I know how to find the triggers that exist on a table, and I know how to enable/disable individual triggers. I know about sp_recompile, but all that does is flag the trigger for recompile at the next execution.

I need to verify whether the trigger is valid without having to actually invoke it. For example, if there's a bad Update trigger, I don't want to actually execute an update on the table.

One example of what I'm dealing with is this... We have Table A and Table B. There is an update trigger on Table B that references column A.col1. Then we alter Table A to drop col1. Later we have to update Table B. At this point the update will fail because of the bad trigger. I want to find and disable the trigger before executing the update on Table B. If there are other triggers on Table B that are valid, I want to leave them alone.Shouldn't this be part of your QA process, not part of your application?

-PatP|||No, it's a database configuration application, and there may be triggers that we don't own and didn't create that we have to disable if they're going to cause a problem in our db config process. If we disable any triggers, we'd report the situation and the customer would be expected to fix the triggers before going live again.sql

how to fire triggers during log shipping

Hi,
We are trying to impliment log shipping. During the log shipping the target database will have insert, update or delete trigger on some of it's table.
Can some one let me know will these trigger get fired during log shipping. if not the is there some way to fire these triggers.

Thanks,
manojTriggers on primary database will have no issues while Log shipping process is on, anyway the secondary server database will be in read-only mode so no affect.|||It means these triggers will never fire on secondary database.
is there any way I can make them fire.

Thanks

Originally posted by Satya
Triggers on primary database will have no issues while Log shipping process is on, anyway the secondary server database will be in read-only mode so no affect.|||Why do you want fire triggers on secondary database, as LS process will restore the transactions from primary database.|||Hi satya,

I need to explain you the scenario

We have two system with two seperate production database on two physicaly seperate servers. one of the production database is search intensive and the other is transaction intensive. There are few common tables in these two databases.

As the data in transaction intensive database changes we want to move this data to the search intensive database to keep in sync.

The client don't want replication as solution.

client is planning to implement the runtime Log shifting for failover database of Transaction intensive database.

So we want to take this opportunity to run triggers on this failover database to move data to search database. as this we think will keep the down time to zero.

any suggestions?

Regards
Manoj

Originally posted by Satya
Why do you want fire triggers on secondary database, as LS process will restore the transactions from primary database.

How to find triggers in a sql server 7 ?

Hello there
Before this weekend I wrote a trigger on a sql 7 DB and sadly enough I
didn't wrote it in the DB documentation.
So now i am standing in front of SQL 7 user DB and want to modify it and
have no idea what the name of it is and how to access it or anything ?
what do I do to find it ? (I used informationschema and it doesn't have
any trigger options in it...) how do I find all triggers in a sql 7 user
db ? how to modify it ?
please help !Schema: How do I show all the triggers in a database?
http://www.aspfaq.com/show.asp?id=2105
AMB
"Simo Sentissi" wrote:
> Hello there
> Before this weekend I wrote a trigger on a sql 7 DB and sadly enough I
> didn't wrote it in the DB documentation.
> So now i am standing in front of SQL 7 user DB and want to modify it and
> have no idea what the name of it is and how to access it or anything ?
> what do I do to find it ? (I used informationschema and it doesn't have
> any trigger options in it...) how do I find all triggers in a sql 7 user
> db ? how to modify it ?
> please help !
>|||"Simo Sentissi" <simo@.sentissiweb.com> wrote in message
news:eg4ienzyEHA.2572@.tk2msftngp13.phx.gbl...
> Hello there
> Before this weekend I wrote a trigger on a sql 7 DB and sadly enough I
> didn't wrote it in the DB documentation.
> So now i am standing in front of SQL 7 user DB and want to modify it and
> have no idea what the name of it is and how to access it or anything ?
> what do I do to find it ? (I used informationschema and it doesn't have
> any trigger options in it...) how do I find all triggers in a sql 7 user
> db ? how to modify it ?
> please help !
SELECT * FROM sysobjects WHERE TYPE = 'TR'
This will give you a list of triggers.
For each trigger row returned, you can find out which table it is attached
to by running the following query and using the parent_obj ID value from the
list of triggers returned.
SELECT * FROM sysobjects WHERE ID = <parent_obj>
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||select *
from sysobjects
where type = 'TR'
You modify the trigger using alter trigger. See books online
topic Alter Trigger for more information.
-Sue
On Mon, 15 Nov 2004 10:37:53 -0700, Simo Sentissi
<simo@.sentissiweb.com> wrote:
>Hello there
>Before this weekend I wrote a trigger on a sql 7 DB and sadly enough I
>didn't wrote it in the DB documentation.
>So now i am standing in front of SQL 7 user DB and want to modify it and
>have no idea what the name of it is and how to access it or anything ?
>what do I do to find it ? (I used informationschema and it doesn't have
>any trigger options in it...) how do I find all triggers in a sql 7 user
>db ? how to modify it ?
>please help !sql

How to find triggers in a sql server 7 ?

Hello there
Before this weekend I wrote a trigger on a sql 7 DB and sadly enough I
didn't wrote it in the DB documentation.
So now i am standing in front of SQL 7 user DB and want to modify it and
have no idea what the name of it is and how to access it or anything ?
what do I do to find it ? (I used informationschema and it doesn't have
any trigger options in it...) how do I find all triggers in a sql 7 user
db ? how to modify it ?
please help !
Schema: How do I show all the triggers in a database?
http://www.aspfaq.com/show.asp?id=2105
AMB
"Simo Sentissi" wrote:

> Hello there
> Before this weekend I wrote a trigger on a sql 7 DB and sadly enough I
> didn't wrote it in the DB documentation.
> So now i am standing in front of SQL 7 user DB and want to modify it and
> have no idea what the name of it is and how to access it or anything ?
> what do I do to find it ? (I used informationschema and it doesn't have
> any trigger options in it...) how do I find all triggers in a sql 7 user
> db ? how to modify it ?
> please help !
>
|||"Simo Sentissi" <simo@.sentissiweb.com> wrote in message
news:eg4ienzyEHA.2572@.tk2msftngp13.phx.gbl...
> Hello there
> Before this weekend I wrote a trigger on a sql 7 DB and sadly enough I
> didn't wrote it in the DB documentation.
> So now i am standing in front of SQL 7 user DB and want to modify it and
> have no idea what the name of it is and how to access it or anything ?
> what do I do to find it ? (I used informationschema and it doesn't have
> any trigger options in it...) how do I find all triggers in a sql 7 user
> db ? how to modify it ?
> please help !
SELECT * FROM sysobjects WHERE TYPE = 'TR'
This will give you a list of triggers.
For each trigger row returned, you can find out which table it is attached
to by running the following query and using the parent_obj ID value from the
list of triggers returned.
SELECT * FROM sysobjects WHERE ID = <parent_obj>
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||select *
from sysobjects
where type = 'TR'
You modify the trigger using alter trigger. See books online
topic Alter Trigger for more information.
-Sue
On Mon, 15 Nov 2004 10:37:53 -0700, Simo Sentissi
<simo@.sentissiweb.com> wrote:

>Hello there
>Before this weekend I wrote a trigger on a sql 7 DB and sadly enough I
>didn't wrote it in the DB documentation.
>So now i am standing in front of SQL 7 user DB and want to modify it and
>have no idea what the name of it is and how to access it or anything ?
>what do I do to find it ? (I used informationschema and it doesn't have
>any trigger options in it...) how do I find all triggers in a sql 7 user
>db ? how to modify it ?
>please help !

How to find triggers in a sql server 7 ?

Hello there
Before this weekend I wrote a trigger on a sql 7 DB and sadly enough I
didn't wrote it in the DB documentation.
So now i am standing in front of SQL 7 user DB and want to modify it and
have no idea what the name of it is and how to access it or anything ?
what do I do to find it ? (I used informationschema and it doesn't have
any trigger options in it...) how do I find all triggers in a sql 7 user
db ? how to modify it ?
please help !Schema: How do I show all the triggers in a database?
http://www.aspfaq.com/show.asp?id=2105
AMB
"Simo Sentissi" wrote:

> Hello there
> Before this weekend I wrote a trigger on a sql 7 DB and sadly enough I
> didn't wrote it in the DB documentation.
> So now i am standing in front of SQL 7 user DB and want to modify it and
> have no idea what the name of it is and how to access it or anything ?
> what do I do to find it ? (I used informationschema and it doesn't have
> any trigger options in it...) how do I find all triggers in a sql 7 user
> db ? how to modify it ?
> please help !
>|||"Simo Sentissi" <simo@.sentissiweb.com> wrote in message
news:eg4ienzyEHA.2572@.tk2msftngp13.phx.gbl...
> Hello there
> Before this weekend I wrote a trigger on a sql 7 DB and sadly enough I
> didn't wrote it in the DB documentation.
> So now i am standing in front of SQL 7 user DB and want to modify it and
> have no idea what the name of it is and how to access it or anything ?
> what do I do to find it ? (I used informationschema and it doesn't have
> any trigger options in it...) how do I find all triggers in a sql 7 user
> db ? how to modify it ?
> please help !
SELECT * FROM sysobjects WHERE TYPE = 'TR'
This will give you a list of triggers.
For each trigger row returned, you can find out which table it is attached
to by running the following query and using the parent_obj ID value from the
list of triggers returned.
SELECT * FROM sysobjects WHERE ID = <parent_obj>
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||select *
from sysobjects
where type = 'TR'
You modify the trigger using alter trigger. See books online
topic Alter Trigger for more information.
-Sue
On Mon, 15 Nov 2004 10:37:53 -0700, Simo Sentissi
<simo@.sentissiweb.com> wrote:

>Hello there
>Before this weekend I wrote a trigger on a sql 7 DB and sadly enough I
>didn't wrote it in the DB documentation.
>So now i am standing in front of SQL 7 user DB and want to modify it and
>have no idea what the name of it is and how to access it or anything ?
>what do I do to find it ? (I used informationschema and it doesn't have
>any trigger options in it...) how do I find all triggers in a sql 7 user
>db ? how to modify it ?
>please help !

How to find the triggers in a database

One of the databases I work with has several triggers and one of them is
throwing an error. I thought I knew which table it is on, but that table has
no triggers. Is there an easy way to identify all the triggers in a
database?
Much obliged,
Geoff.select * from sysobjects where xtype ='tr'
http://sqlservercode.blogspot.com/|||Hi
Try
select name from dbo.sysobjects
where Xtype='TR'
lookup OBJECTPROPERTY command which has one opf the many parameters like
'TableHasUpdateTrigger'
"Geoff Pennington" <Geoff.Pennington.ctr@.whs.mil.no_spam> wrote in message
news:OHnL5ajBGHA.1372@.TK2MSFTNGP14.phx.gbl...
> One of the databases I work with has several triggers and one of them is
> throwing an error. I thought I knew which table it is on, but that table
> has no triggers. Is there an easy way to identify all the triggers in a
> database?
> Much obliged,
> Geoff.
>|||Run this query on any db and you will get the listin of all tables that hav
triggers on them
select TBL.name as "Table", TRG.name as "Trigger"
from sysobjects TRG
inner join sysobjects TBL on TRG.parent_obj = TBL.id
where TRG.xtype = 'tr'
order by TBL.name, TRG.name
Robert
"Geoff Pennington" <Geoff.Pennington.ctr@.whs.mil.no_spam> wrote in message
news:OHnL5ajBGHA.1372@.TK2MSFTNGP14.phx.gbl...
> One of the databases I work with has several triggers and one of them is
> throwing an error. I thought I knew which table it is on, but that table
has
> no triggers. Is there an easy way to identify all the triggers in a
> database?
> Much obliged,
> Geoff.
>|||http://www.aspfaq.com/2105
"Geoff Pennington" <Geoff.Pennington.ctr@.whs.mil.no_spam> wrote in message
news:OHnL5ajBGHA.1372@.TK2MSFTNGP14.phx.gbl...
> One of the databases I work with has several triggers and one of them is
> throwing an error. I thought I knew which table it is on, but that table
> has no triggers. Is there an easy way to identify all the triggers in a
> database?
> Much obliged,
> Geoff.
>|||select * from sysobjects where OBJECTPROPERTY(id, N'IsTrigger') = 1
if you then know the name of the trigger you can write
select * from sysobjects where id = object_id(N'[dbo].[TriggerName]') and
OBJECTPROPERTY(id, N'IsTrigger') = 1|||Thanks. Good link.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1135174145.929774.170520@.o13g2000cwo.googlegroups.com...
> select * from sysobjects where xtype ='tr'
>
> http://sqlservercode.blogspot.com/
>|||Thanks.
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:OICVJjjBGHA.2356@.tk2msftngp13.phx.gbl...
> Run this query on any db and you will get the listin of all tables that
> hav
> triggers on them
> select TBL.name as "Table", TRG.name as "Trigger"
> from sysobjects TRG
> inner join sysobjects TBL on TRG.parent_obj = TBL.id
> where TRG.xtype = 'tr'
> order by TBL.name, TRG.name
> Robert
>
> "Geoff Pennington" <Geoff.Pennington.ctr@.whs.mil.no_spam> wrote in message
> news:OHnL5ajBGHA.1372@.TK2MSFTNGP14.phx.gbl...
> has
>|||sp_msforeachtable 'exec sp_helptrigger ''?'''
Regards
Amish

How to find how a record was deleted

Is there a way to tell who or what process deleted a record in a SQL table.
I know you can setup triggers or Profiler - but is there a way to see how a
record was deleted if you DO NOT have a trigger or Profiler already running?
This is SQL 2000 Standard, sp3a with default options and configuration.

ThanksHi rdraider,
It may help your cause
Step 1 : Login as sa or user with sa rights
Step 2 : Run a trace with minimum TSQL (Statement Start , Statement
Complete)
If you wish to know from where(EM or SQL Query Analyzer) record is
deleted then the column Applicationname of Trace output will help you.

With warm regards
Jatinder|||If you have log backups, then there are several third-party tools which
can read them and recover information about when data was deleted. For
example:

http://www.lumigent.com/products/le_sql.html

Simon