Disabling Foreign Key Constraint in Trigger

Hey,
Can anybody tell me how to disable a Foreign Key contraint from executing
while a Trigger is executing on DELETE event?
Best regards,
BillDRI are checked berofe the AFTER TRIGGERS, so if it is a problem the trigger
will not be fired.
AMB
"AST" wrote:

> Hey,
> Can anybody tell me how to disable a Foreign Key contraint from executing
> while a Trigger is executing on DELETE event?
> Best regards,
> Bill
>
>|||Hey AMB,
The table [HistoryDetails] has a FK on the column HistoryID that is being
used to enforce integrity with the table[History].
I am not sure if you are stating you can't disable the FK or if I have to
modify the context of the Trigger?
Best regards,
Bill
The Trigger is defined as:
/****** Object: Trigger dbo.TRIGGER_CascDeleteOnHistoryDetails Script
Date: 5/24/2002 12:14:53 AM ******/
CREATE TRIGGER [TRIGGER_CascDeleteOnHistoryDetails] ON dbo.History
FOR DELETE
AS
DELETE HistoryDetails FROM deleted, HistoryDetails WHERE deleted.HistoryID =
HistoryDetails.HistoryID
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:4662B0B5-25C5-4217-84F0-B75E5ECCF604@.microsoft.com...
> DRI are checked berofe the AFTER TRIGGERS, so if it is a problem the
trigger
> will not be fired.
>
> AMB
>
> "AST" wrote:
>
executing|||That will make quite a mess:
1) Disabling a Foreign Key constraint is Data Definition Language, and can
only be executed by the table owner and members of the symin fixed server
roles and the db_owner and db_ddladmin database roles, and is not
transferable. So you have to give your users a large number of unnecessary
permissions.
2) Disabling a foreign key works for all connections, not just the current
one. So while you disable it in your trigger, someone else might need it.
What exactly are you trying to achieve? This is definitely not the right
way.
Jacco Schalkwijk
SQL Server MVP
"AST" <no_reply@.please.com> wrote in message
news:O50z0N6EFHA.3672@.TK2MSFTNGP14.phx.gbl...
> Hey,
> Can anybody tell me how to disable a Foreign Key contraint from executing
> while a Trigger is executing on DELETE event?
> Best regards,
> Bill
>|||Hey Jacco,
What I am trying to achieve is the following:
I have 2 tables [History], [HistoryDetails] where the Primary Table
[History] mantains detailed data in the [HistoryDetails] table. The key
relationship is History.HistoryID=> HistoryDetails.HistoryID.
I have place a constraint on the [HistoryDetails] table such that a record
cannot be added unless the HistoryID is existing in the [History] table.
I had also created a trigger to delete all relational records in the
[HistoryDetails] table when the Primary Key record in the [History] table
was deleted.
This is where I run into problems because the execution of the trigger fails
due to the FK Constraint.
Any suggestions would be very welcome?
Best regards,
Bill
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:uEUZEs6EFHA.2700@.TK2MSFTNGP14.phx.gbl...
> That will make quite a mess:
> 1) Disabling a Foreign Key constraint is Data Definition Language, and can
> only be executed by the table owner and members of the symin fixed
server
> roles and the db_owner and db_ddladmin database roles, and is not
> transferable. So you have to give your users a large number of unnecessary
> permissions.
> 2) Disabling a foreign key works for all connections, not just the current
> one. So while you disable it in your trigger, someone else might need it.
> What exactly are you trying to achieve? This is definitely not the right
> way.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "AST" <no_reply@.please.com> wrote in message
> news:O50z0N6EFHA.3672@.TK2MSFTNGP14.phx.gbl...
executing
>|||Drop the trigger and use the ON DELETE CASCADE option on the foreign
key constraint.
David Portas
SQL Server MVP
--|||Hey David,
I don't think this is supported in SQL 7 and I have to support both SQL7 and
SQL2000
I tried running the SQL to add this constraint in SQL7 and it fails and
doesn't seem to recognize anthing to do with cascaded deletes?
Best regards,
Bill
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108507933.414888.306670@.l41g2000cwc.googlegroups.com...
> Drop the trigger and use the ON DELETE CASCADE option on the foreign
> key constraint.
> --
> David Portas
> SQL Server MVP
> --
>|||AST
Well, you have to drop a FK constraint and to allow deletion within a
trigger.
"AST" <no_reply@.please.com> wrote in message
news:emTg1W8EFHA.624@.TK2MSFTNGP09.phx.gbl...
> Hey David,
> I don't think this is supported in SQL 7 and I have to support both SQL7
and
> SQL2000
> I tried running the SQL to add this constraint in SQL7 and it fails and
> doesn't seem to recognize anthing to do with cascaded deletes?
> Best regards,
> Bill
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1108507933.414888.306670@.l41g2000cwc.googlegroups.com...
>