"Rich" <R***@discussions.microsoft.com> wrote in message
news:ED97EF6C-6223-457C-A668-7BE409CAE593@microsoft.com...
> Hello,
>
> I copied a database from our live server to a test server where I could
> study the database, experiment and so on.  I cannot delete/remove a row from
> the primary table.   The table had several relationships, dozens of triggers,
> a constraint, and was under replication on the live server.
>
> I have removed all the relationships, triggers, constraints from the table
> on the test server, and I do not have replication running on the test server
> and there is no replication running on the database in the test server.
>
> I did copy the contents of the primary table to a temp table and was able to
> delete the row from the temp table.  But I can't delete from the primary
> table.  Is there a property somewhere in the database that I need to look at?
> How come I can't delete from this table but I was able to delete from the
> temp table?  Is there an SP I can run to fix this or check what is going on?
>
> Thanks,
> Rich

"Tibor Karaszi" wrote:

> Can you post the DELETE statement and the error message?
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
>
> "Rich" <R***@discussions.microsoft.com> wrote in message
> news:ED97EF6C-6223-457C-A668-7BE409CAE593@microsoft.com...
> > Hello,
> >
> > I copied a database from our live server to a test server where I could
> > study the database, experiment and so on.  I cannot delete/remove a row from
> > the primary table.   The table had several relationships, dozens of triggers,
> > a constraint, and was under replication on the live server.
> >
> > I have removed all the relationships, triggers, constraints from the table
> > on the test server, and I do not have replication running on the test server
> > and there is no replication running on the database in the test server.
> >
> > I did copy the contents of the primary table to a temp table and was able to
> > delete the row from the temp table.  But I can't delete from the primary
> > table.  Is there a property somewhere in the database that I need to look at?
> > How come I can't delete from this table but I was able to delete from the
> > temp table?  Is there an SP I can run to fix this or check what is going on?
> >
> > Thanks,
> > Rich
>
>
>

"Rich" <R***@discussions.microsoft.com> wrote in message
news:CFE2CC3A-B11A-4234-A99B-B2798AE2FBDC@microsoft.com...
> Delete from PrimaryTbl Where RecordID = 12345
>
> --No error messages  The message just says 1 row affected
>
> When I select from PrimaryTbl where RecordID = 12345
>
> the record is still there.
>
> But if I say Select * into Temp From primaryTbl
>
> Temp gets all the rows.  I check if recordID = 12345 row is in Temp.  It is.
> Then I say Delete Temp Where RecordID = 12345.  Then the row is gone from
> Temp.   What is going on with PrimaryTbl?  I have removed all indexes,
> relationships, constraints, etc.
>
>
>
> "Tibor Karaszi" wrote:
>
>> Can you post the DELETE statement and the error message?
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>>
>> "Rich" <R***@discussions.microsoft.com> wrote in message
>> news:ED97EF6C-6223-457C-A668-7BE409CAE593@microsoft.com...
>> > Hello,
>> >
>> > I copied a database from our live server to a test server where I could
>> > study the database, experiment and so on.  I cannot delete/remove a row from
>> > the primary table.   The table had several relationships, dozens of triggers,
>> > a constraint, and was under replication on the live server.
>> >
>> > I have removed all the relationships, triggers, constraints from the table
>> > on the test server, and I do not have replication running on the test server
>> > and there is no replication running on the database in the test server.
>> >
>> > I did copy the contents of the primary table to a temp table and was able to
>> > delete the row from the temp table.  But I can't delete from the primary
>> > table.  Is there a property somewhere in the database that I need to look at?
>> > How come I can't delete from this table but I was able to delete from the
>> > temp table?  Is there an SP I can run to fix this or check what is going on?
>> >
>> > Thanks,
>> > Rich
>>
>>
>>

"Tibor Karaszi" wrote:

> Trigger? Also, what does the execution plan for the DELETE statement look like? If it isn't a
> trigger, it smells like either a corrupt database or a bug in SQL Server.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
>
> "Rich" <R***@discussions.microsoft.com> wrote in message
> news:CFE2CC3A-B11A-4234-A99B-B2798AE2FBDC@microsoft.com...
> > Delete from PrimaryTbl Where RecordID = 12345
> >
> > --No error messages  The message just says 1 row affected
> >
> > When I select from PrimaryTbl where RecordID = 12345
> >
> > the record is still there.
> >
> > But if I say Select * into Temp From primaryTbl
> >
> > Temp gets all the rows.  I check if recordID = 12345 row is in Temp.  It is.
> > Then I say Delete Temp Where RecordID = 12345.  Then the row is gone from
> > Temp.   What is going on with PrimaryTbl?  I have removed all indexes,
> > relationships, constraints, etc.
> >
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> Can you post the DELETE statement and the error message?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Rich" <R***@discussions.microsoft.com> wrote in message
> >> news:ED97EF6C-6223-457C-A668-7BE409CAE593@microsoft.com...
> >> > Hello,
> >> >
> >> > I copied a database from our live server to a test server where I could
> >> > study the database, experiment and so on.  I cannot delete/remove a row from
> >> > the primary table.   The table had several relationships, dozens of triggers,
> >> > a constraint, and was under replication on the live server.
> >> >
> >> > I have removed all the relationships, triggers, constraints from the table
> >> > on the test server, and I do not have replication running on the test server
> >> > and there is no replication running on the database in the test server.
> >> >
> >> > I did copy the contents of the primary table to a temp table and was able to
> >> > delete the row from the temp table.  But I can't delete from the primary
> >> > table.  Is there a property somewhere in the database that I need to look at?
> >> > How come I can't delete from this table but I was able to delete from the
> >> > temp table?  Is there an SP I can run to fix this or check what is going on?
> >> >
> >> > Thanks,
> >> > Rich
> >>
> >>
> >>
>
>
>

"Rich" <R***@discussions.microsoft.com> wrote in message
news:F341B6A6-D370-434E-89FB-9E0A7161CC1E@microsoft.com...
>I will try to recreate the table with all the triggers, constraints, indexes,
> etc and drop the old table.  Maybe the table is corrupt.
>
> "Tibor Karaszi" wrote:
>
>> Trigger? Also, what does the execution plan for the DELETE statement look like? If it isn't a
>> trigger, it smells like either a corrupt database or a bug in SQL Server.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>>
>> "Rich" <R***@discussions.microsoft.com> wrote in message
>> news:CFE2CC3A-B11A-4234-A99B-B2798AE2FBDC@microsoft.com...
>> > Delete from PrimaryTbl Where RecordID = 12345
>> >
>> > --No error messages  The message just says 1 row affected
>> >
>> > When I select from PrimaryTbl where RecordID = 12345
>> >
>> > the record is still there.
>> >
>> > But if I say Select * into Temp From primaryTbl
>> >
>> > Temp gets all the rows.  I check if recordID = 12345 row is in Temp.  It is.
>> > Then I say Delete Temp Where RecordID = 12345.  Then the row is gone from
>> > Temp.   What is going on with PrimaryTbl?  I have removed all indexes,
>> > relationships, constraints, etc.
>> >
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Can you post the DELETE statement and the error message?
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "Rich" <R***@discussions.microsoft.com> wrote in message
>> >> news:ED97EF6C-6223-457C-A668-7BE409CAE593@microsoft.com...
>> >> > Hello,
>> >> >
>> >> > I copied a database from our live server to a test server where I could
>> >> > study the database, experiment and so on.  I cannot delete/remove a row from
>> >> > the primary table.   The table had several relationships, dozens of triggers,
>> >> > a constraint, and was under replication on the live server.
>> >> >
>> >> > I have removed all the relationships, triggers, constraints from the table
>> >> > on the test server, and I do not have replication running on the test server
>> >> > and there is no replication running on the database in the test server.
>> >> >
>> >> > I did copy the contents of the primary table to a temp table and was able to
>> >> > delete the row from the temp table.  But I can't delete from the primary
>> >> > table.  Is there a property somewhere in the database that I need to look at?
>> >> > How come I can't delete from this table but I was able to delete from the
>> >> > temp table?  Is there an SP I can run to fix this or check what is going on?
>> >> >
>> >> > Thanks,
>> >> > Rich
>> >>
>> >>
>> >>
>>
>>
>>

"Rich" <R***@discussions.microsoft.com> wrote in message
news:ED97EF6C-6223-457C-A668-7BE409CAE593@microsoft.com...
> Hello,
>
> I copied a database from our live server to a test server where I could
> study the database, experiment and so on.  I cannot delete/remove a row
> from
> the primary table.   The table had several relationships, dozens of
> triggers,
> a constraint, and was under replication on the live server.
>
> I have removed all the relationships, triggers, constraints from the table
> on the test server, and I do not have replication running on the test
> server
> and there is no replication running on the database in the test server.
>
> I did copy the contents of the primary table to a temp table and was able
> to
> delete the row from the temp table.  But I can't delete from the primary
> table.  Is there a property somewhere in the database that I need to look
> at?
> How come I can't delete from this table but I was able to delete from the
> temp table?  Is there an SP I can run to fix this or check what is going
> on?
>
> Thanks,
> Rich

"Jeff Dillon" <jeffdil***@hotmail.com> wrote in message
news:OqFSPqreGHA.5040@TK2MSFTNGP03.phx.gbl...
> Dozens of triggers? A table can generally only have 3 - INSERT, UPDATE, DELETE
>
> "Rich" <R***@discussions.microsoft.com> wrote in message
> news:ED97EF6C-6223-457C-A668-7BE409CAE593@microsoft.com...
>> Hello,
>>
>> I copied a database from our live server to a test server where I could
>> study the database, experiment and so on.  I cannot delete/remove a row from
>> the primary table.   The table had several relationships, dozens of triggers,
>> a constraint, and was under replication on the live server.
>>
>> I have removed all the relationships, triggers, constraints from the table
>> on the test server, and I do not have replication running on the test server
>> and there is no replication running on the database in the test server.
>>
>> I did copy the contents of the primary table to a temp table and was able to
>> delete the row from the temp table.  But I can't delete from the primary
>> table.  Is there a property somewhere in the database that I need to look at?
>> How come I can't delete from this table but I was able to delete from the
>> temp table?  Is there an SP I can run to fix this or check what is going on?
>>
>> Thanks,
>> Rich
>
>

"Tibor Karaszi" wrote:

> Jeff,
>
> As of 7.0 you can have several triggers of the same type on a table. And as of 2000, you can define
> which to fire first and which to fire last.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
>
> "Jeff Dillon" <jeffdil***@hotmail.com> wrote in message
> news:OqFSPqreGHA.5040@TK2MSFTNGP03.phx.gbl...
> > Dozens of triggers? A table can generally only have 3 - INSERT, UPDATE, DELETE
> >
> > "Rich" <R***@discussions.microsoft.com> wrote in message
> > news:ED97EF6C-6223-457C-A668-7BE409CAE593@microsoft.com...
> >> Hello,
> >>
> >> I copied a database from our live server to a test server where I could
> >> study the database, experiment and so on.  I cannot delete/remove a row from
> >> the primary table.   The table had several relationships, dozens of triggers,
> >> a constraint, and was under replication on the live server.
> >>
> >> I have removed all the relationships, triggers, constraints from the table
> >> on the test server, and I do not have replication running on the test server
> >> and there is no replication running on the database in the test server.
> >>
> >> I did copy the contents of the primary table to a temp table and was able to
> >> delete the row from the temp table.  But I can't delete from the primary
> >> table.  Is there a property somewhere in the database that I need to look at?
> >> How come I can't delete from this table but I was able to delete from the
> >> temp table?  Is there an SP I can run to fix this or check what is going on?
> >>
> >> Thanks,
> >> Rich
> >
> >
>
>
>

"Rich" <R***@discussions.microsoft.com> wrote in message
news:E95574E9-7F0F-474A-B870-7EDECDAC423C@microsoft.com...
> Yes.  The table in question had like 31 triggers - 11 or 12 inserts, some
> updates and there was a trigger to rollback deletes.  But I commented out
> all
> of the triggers - all of them.  So there are basically no triggers except
> for
>
> t_sometinging on PrimaryTbl
> For Insert...
> As
> /* */
> Return
>
> I did that to all of the triggers so that nothing would fire.  Right now I
> am recreating the table with all the triggers, indexes, relationships,
> constraints and so on and see if that table works (minus of course, the
> For
> Delte As Rollback...)
>
>
> "Tibor Karaszi" wrote:
>
>> Jeff,
>>
>> As of 7.0 you can have several triggers of the same type on a table. And
>> as of 2000, you can define
>> which to fire first and which to fire last.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>>
>> "Jeff Dillon" <jeffdil***@hotmail.com> wrote in message
>> news:OqFSPqreGHA.5040@TK2MSFTNGP03.phx.gbl...
>> > Dozens of triggers? A table can generally only have 3 - INSERT, UPDATE,
>> > DELETE
>> >
>> > "Rich" <R***@discussions.microsoft.com> wrote in message
>> > news:ED97EF6C-6223-457C-A668-7BE409CAE593@microsoft.com...
>> >> Hello,
>> >>
>> >> I copied a database from our live server to a test server where I
>> >> could
>> >> study the database, experiment and so on.  I cannot delete/remove a
>> >> row from
>> >> the primary table.   The table had several relationships, dozens of
>> >> triggers,
>> >> a constraint, and was under replication on the live server.
>> >>
>> >> I have removed all the relationships, triggers, constraints from the
>> >> table
>> >> on the test server, and I do not have replication running on the test
>> >> server
>> >> and there is no replication running on the database in the test
>> >> server.
>> >>
>> >> I did copy the contents of the primary table to a temp table and was
>> >> able to
>> >> delete the row from the temp table.  But I can't delete from the
>> >> primary
>> >> table.  Is there a property somewhere in the database that I need to
>> >> look at?
>> >> How come I can't delete from this table but I was able to delete from
>> >> the
>> >> temp table?  Is there an SP I can run to fix this or check what is
>> >> going on?
>> >>
>> >> Thanks,
>> >> Rich
>> >
>> >
>>
>>
>>

"Kalen Delaney" wrote:

> A much easier way to disable triggers, that doesn't involve having to change
> code, is to use ALTER TABLE ... DISABLE TRIGGER
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
>
> "Rich" <R***@discussions.microsoft.com> wrote in message
> news:E95574E9-7F0F-474A-B870-7EDECDAC423C@microsoft.com...
> > Yes.  The table in question had like 31 triggers - 11 or 12 inserts, some
> > updates and there was a trigger to rollback deletes.  But I commented out
> > all
> > of the triggers - all of them.  So there are basically no triggers except
> > for
> >
> > t_sometinging on PrimaryTbl
> > For Insert...
> > As
> > /* */
> > Return
> >
> > I did that to all of the triggers so that nothing would fire.  Right now I
> > am recreating the table with all the triggers, indexes, relationships,
> > constraints and so on and see if that table works (minus of course, the
> > For
> > Delte As Rollback...)
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> Jeff,
> >>
> >> As of 7.0 you can have several triggers of the same type on a table. And
> >> as of 2000, you can define
> >> which to fire first and which to fire last.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Jeff Dillon" <jeffdil***@hotmail.com> wrote in message
> >> news:OqFSPqreGHA.5040@TK2MSFTNGP03.phx.gbl...
> >> > Dozens of triggers? A table can generally only have 3 - INSERT, UPDATE,
> >> > DELETE
> >> >
> >> > "Rich" <R***@discussions.microsoft.com> wrote in message
> >> > news:ED97EF6C-6223-457C-A668-7BE409CAE593@microsoft.com...
> >> >> Hello,
> >> >>
> >> >> I copied a database from our live server to a test server where I
> >> >> could
> >> >> study the database, experiment and so on.  I cannot delete/remove a
> >> >> row from
> >> >> the primary table.   The table had several relationships, dozens of
> >> >> triggers,
> >> >> a constraint, and was under replication on the live server.
> >> >>
> >> >> I have removed all the relationships, triggers, constraints from the
> >> >> table
> >> >> on the test server, and I do not have replication running on the test
> >> >> server
> >> >> and there is no replication running on the database in the test
> >> >> server.
> >> >>
> >> >> I did copy the contents of the primary table to a temp table and was
> >> >> able to
> >> >> delete the row from the temp table.  But I can't delete from the
> >> >> primary
> >> >> table.  Is there a property somewhere in the database that I need to
> >> >> look at?
> >> >> How come I can't delete from this table but I was able to delete from
> >> >> the
> >> >> temp table?  Is there an SP I can run to fix this or check what is
> >> >> going on?
> >> >>
> >> >> Thanks,
> >> >> Rich
> >> >
> >> >
> >>
> >>
> >>
>
>
>

"Rich" wrote:

> yes, I have used that before, but I just needed to make sure there was
> nothing in the triggers that could possibly run.
>
> Well, as fate would have it, after I recreated my table and was able to
> delete the desired row, I then readded the row and also re-added all the
> triggers, relationships, etc.  Now I can't delete the row again in the new
> table.  So I guess maybe there was nothing wrong with the original table. 
>
> So tommorrow I have to play the boring game of recreating the table, add the
> row, delete the row, and keep adding triggers one at a time and relationships
> until I can't delete the row and thus isolate the offending procedure,
> relationship.  I am already having indigestion thinking about it.
>
> "Kalen Delaney" wrote:
>
> > A much easier way to disable triggers, that doesn't involve having to change
> > code, is to use ALTER TABLE ... DISABLE TRIGGER
> >
> > --
> > HTH
> > Kalen Delaney, SQL Server MVP
> > www.solidqualitylearning.com
> >
> >
> > "Rich" <R***@discussions.microsoft.com> wrote in message
> > news:E95574E9-7F0F-474A-B870-7EDECDAC423C@microsoft.com...
> > > Yes.  The table in question had like 31 triggers - 11 or 12 inserts, some
> > > updates and there was a trigger to rollback deletes.  But I commented out
> > > all
> > > of the triggers - all of them.  So there are basically no triggers except
> > > for
> > >
> > > t_sometinging on PrimaryTbl
> > > For Insert...
> > > As
> > > /* */
> > > Return
> > >
> > > I did that to all of the triggers so that nothing would fire.  Right now I
> > > am recreating the table with all the triggers, indexes, relationships,
> > > constraints and so on and see if that table works (minus of course, the
> > > For
> > > Delte As Rollback...)
> > >
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > >> Jeff,
> > >>
> > >> As of 7.0 you can have several triggers of the same type on a table. And
> > >> as of 2000, you can define
> > >> which to fire first and which to fire last.
> > >>
> > >> --
> > >> Tibor Karaszi, SQL Server MVP
> > >> http://www.karaszi.com/sqlserver/default.asp
> > >> http://www.solidqualitylearning.com/
> > >>
> > >>
> > >> "Jeff Dillon" <jeffdil***@hotmail.com> wrote in message
> > >> news:OqFSPqreGHA.5040@TK2MSFTNGP03.phx.gbl...
> > >> > Dozens of triggers? A table can generally only have 3 - INSERT, UPDATE,
> > >> > DELETE
> > >> >
> > >> > "Rich" <R***@discussions.microsoft.com> wrote in message
> > >> > news:ED97EF6C-6223-457C-A668-7BE409CAE593@microsoft.com...
> > >> >> Hello,
> > >> >>
> > >> >> I copied a database from our live server to a test server where I
> > >> >> could
> > >> >> study the database, experiment and so on.  I cannot delete/remove a
> > >> >> row from
> > >> >> the primary table.   The table had several relationships, dozens of
> > >> >> triggers,
> > >> >> a constraint, and was under replication on the live server.
> > >> >>
> > >> >> I have removed all the relationships, triggers, constraints from the
> > >> >> table
> > >> >> on the test server, and I do not have replication running on the test
> > >> >> server
> > >> >> and there is no replication running on the database in the test
> > >> >> server.
> > >> >>
> > >> >> I did copy the contents of the primary table to a temp table and was
> > >> >> able to
> > >> >> delete the row from the temp table.  But I can't delete from the
> > >> >> primary
> > >> >> table.  Is there a property somewhere in the database that I need to
> > >> >> look at?
> > >> >> How come I can't delete from this table but I was able to delete from
> > >> >> the
> > >> >> temp table?  Is there an SP I can run to fix this or check what is
> > >> >> going on?
> > >> >>
> > >> >> Thanks,
> > >> >> Rich
> > >> >
> > >> >
> > >>
> > >>
> > >>
> >
> >
> >

"Rich" <R***@discussions.microsoft.com> wrote in message
news:92D437D9-CFDB-45F4-8991-90C49BBFD1E7@microsoft.com...
> Well, I figured out what the problem was in deleteing rows on my table.
It
> was the Instead Of Delete trigger.  Even though I had commented out the
body
> and only had
>
> Create Trigger...
> Instead Of Delete
> As
> --
> --
> Return
>
> This would not allow deletes on the table.  When I removed the trigger
> entirely, I was able to delete rows.
>
> "Rich" wrote:
>
> > yes, I have used that before, but I just needed to make sure there was
> > nothing in the triggers that could possibly run.
> >
> > Well, as fate would have it, after I recreated my table and was able to
> > delete the desired row, I then readded the row and also re-added all the
> > triggers, relationships, etc.  Now I can't delete the row again in the
new
> > table.  So I guess maybe there was nothing wrong with the original
table.
> >
> > So tommorrow I have to play the boring game of recreating the table, add
the
> > row, delete the row, and keep adding triggers one at a time and
relationships
> > until I can't delete the row and thus isolate the offending procedure,
> > relationship.  I am already having indigestion thinking about it.
> >
> > "Kalen Delaney" wrote:
> >
> > > A much easier way to disable triggers, that doesn't involve having to
change
> > > code, is to use ALTER TABLE ... DISABLE TRIGGER
> > >
> > > --
> > > HTH
> > > Kalen Delaney, SQL Server MVP
> > > www.solidqualitylearning.com
> > >
> > >
> > > "Rich" <R***@discussions.microsoft.com> wrote in message
> > > news:E95574E9-7F0F-474A-B870-7EDECDAC423C@microsoft.com...
> > > > Yes.  The table in question had like 31 triggers - 11 or 12 inserts,
some
> > > > updates and there was a trigger to rollback deletes.  But I
commented out
> > > > all
> > > > of the triggers - all of them.  So there are basically no triggers
except
> > > > for
> > > >
> > > > t_sometinging on PrimaryTbl
> > > > For Insert...
> > > > As
> > > > /* */
> > > > Return
> > > >
> > > > I did that to all of the triggers so that nothing would fire.  Right
now I
> > > > am recreating the table with all the triggers, indexes,
relationships,
> > > > constraints and so on and see if that table works (minus of course,
the
> > > > For
> > > > Delte As Rollback...)
> > > >
> > > >
> > > > "Tibor Karaszi" wrote:
> > > >
> > > >> Jeff,
> > > >>
> > > >> As of 7.0 you can have several triggers of the same type on a
table. And
> > > >> as of 2000, you can define
> > > >> which to fire first and which to fire last.
> > > >>
> > > >> --
> > > >> Tibor Karaszi, SQL Server MVP
> > > >> http://www.karaszi.com/sqlserver/default.asp
> > > >> http://www.solidqualitylearning.com/
> > > >>
> > > >>
> > > >> "Jeff Dillon" <jeffdil***@hotmail.com> wrote in message
> > > >> news:OqFSPqreGHA.5040@TK2MSFTNGP03.phx.gbl...
> > > >> > Dozens of triggers? A table can generally only have 3 - INSERT,
UPDATE,
> > > >> > DELETE
> > > >> >
> > > >> > "Rich" <R***@discussions.microsoft.com> wrote in message
> > > >> > news:ED97EF6C-6223-457C-A668-7BE409CAE593@microsoft.com...
> > > >> >> Hello,
> > > >> >>
> > > >> >> I copied a database from our live server to a test server where
I
> > > >> >> could
> > > >> >> study the database, experiment and so on.  I cannot

> > > >> >> row from
> > > >> >> the primary table.   The table had several relationships, dozens
of
> > > >> >> triggers,
> > > >> >> a constraint, and was under replication on the live server.
> > > >> >>
> > > >> >> I have removed all the relationships, triggers, constraints from
the
> > > >> >> table
> > > >> >> on the test server, and I do not have replication running on the
test
> > > >> >> server
> > > >> >> and there is no replication running on the database in the test
> > > >> >> server.
> > > >> >>
> > > >> >> I did copy the contents of the primary table to a temp table and
was
> > > >> >> able to
> > > >> >> delete the row from the temp table.  But I can't delete from the
> > > >> >> primary
> > > >> >> table.  Is there a property somewhere in the database that I
need to
> > > >> >> look at?
> > > >> >> How come I can't delete from this table but I was able to delete
from
> > > >> >> the
> > > >> >> temp table?  Is there an SP I can run to fix this or check what
is
> > > >> >> going on?
> > > >> >>
> > > >> >> Thanks,
> > > >> >> Rich
> > > >> >
> > > >> >
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >

"Rich" wrote:

> Yes.  The table in question had like 31 triggers - 11 or 12 inserts, some
> updates and there was a trigger to rollback deletes.  But I commented out all
> of the triggers - all of them.  So there are basically no triggers except for
>
> t_sometinging on PrimaryTbl
> For Insert...
> As
> /* */
> Return
>
> I did that to all of the triggers so that nothing would fire.  Right now I
> am recreating the table with all the triggers, indexes, relationships,
> constraints and so on and see if that table works (minus of course, the For
> Delte As Rollback...)
>
>
> "Tibor Karaszi" wrote:
>
> > Jeff,
> >
> > As of 7.0 you can have several triggers of the same type on a table. And as of 2000, you can define
> > which to fire first and which to fire last.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Jeff Dillon" <jeffdil***@hotmail.com> wrote in message
> > news:OqFSPqreGHA.5040@TK2MSFTNGP03.phx.gbl...
> > > Dozens of triggers? A table can generally only have 3 - INSERT, UPDATE, DELETE
> > >
> > > "Rich" <R***@discussions.microsoft.com> wrote in message
> > > news:ED97EF6C-6223-457C-A668-7BE409CAE593@microsoft.com...
> > >> Hello,
> > >>
> > >> I copied a database from our live server to a test server where I could
> > >> study the database, experiment and so on.  I cannot delete/remove a row from
> > >> the primary table.   The table had several relationships, dozens of triggers,
> > >> a constraint, and was under replication on the live server.
> > >>
> > >> I have removed all the relationships, triggers, constraints from the table
> > >> on the test server, and I do not have replication running on the test server
> > >> and there is no replication running on the database in the test server.
> > >>
> > >> I did copy the contents of the primary table to a temp table and was able to
> > >> delete the row from the temp table.  But I can't delete from the primary
> > >> table.  Is there a property somewhere in the database that I need to look at?
> > >> How come I can't delete from this table but I was able to delete from the
> > >> temp table?  Is there an SP I can run to fix this or check what is going on?
> > >>
> > >> Thanks,
> > >> Rich
> > >
> > >
> >
> >
> >