"Chris" <cw@community.nospam> wrote in message
news:#XtVWKktFHA.3896@TK2MSFTNGP15.phx.gbl...
> I have created a many-to-many relationship on the following tables...
> CommissionCodes, ItemCodes and the junction table Commission.  Both sides
of
> the many-to-many relationship have a cascade delete.
>
> I would like to prevent deletions from the Commission table directly.  The
> only way rows should be allowed to be removed from this table is by
deleting
> a row from the CommissionCodes table or the ItemCodes table and a cascade
> delete is performed.  Therefore I tried to create a trigger to prevent
> direct deletions.  Here is what i've created so far...
>
> CREATE TRIGGER [trigDeleteCommission] ON dbo.Commission
> FOR DELETE
> AS
> IF (@ROWCOUNT = 0)
>     RETURN
> BEGIN
>
> IF EXISTS(SELECT * FROM CommissionCodesWHERE CommissionCodeSerialNo IN
> (SELECT CommissionCodeSerialNo FROM deleted)) AND EXISTS(SELECT * FROM
> ItemCodes WHERE ItemCodeSerialNo IN (SELECT ItemCodeSerialNo FROM
deleted))
>     BEGIN
>     RAISERROR('Rows cannot be removed directly!',16,1)
>     ROLLBACK TRANSACTION
>     END
> END
>
>
> This seems to work but I was wondering if this could possibly go wrong?
> Does this approach seem okay or is there a better way to achieve this?
>
> The code to create the tables is below...
>
> Thanks,
> Chris
>
>
>
>
> CREATE TABLE [dbo].[CommissionCodes] (
>  [CommissionCodeSerialNo] [int] IDENTITY (1, 1) NOT NULL ,
>  [Code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>  [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[ItemCodes] (
>  [ItemCodeSerialNo] [int] IDENTITY (1, 1) NOT NULL ,
>  [Code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>  [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Commission] (
>  [CommissionCodeSerialNo] [int] NOT NULL ,
>  [ItemCodeSerialNo] [int] NOT NULL ,
>  [Percentage] [numeric](5, 2) NULL ,
>  [Amount] [money] NULL ,
>  [Minimum] [money] NULL ,
>  [Maximum] [money] NULL
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[CommissionCodes] WITH NOCHECK ADD
>  CONSTRAINT [PK_CommissionCodes] PRIMARY KEY  CLUSTERED
>  (
>   [CommissionCodeSerialNo]
>  )  ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[ItemCodes] WITH NOCHECK ADD
>  CONSTRAINT [PK_ItemCodes] PRIMARY KEY  CLUSTERED
>  (
>   [ItemCodeSerialNo]
>  )  ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Commission] WITH NOCHECK ADD
>  CONSTRAINT [PK_Commission] PRIMARY KEY  CLUSTERED
>  (
>   [CommissionCodeSerialNo],
>   [ItemCodeSerialNo]
>  )  ON [PRIMARY]
> GO
>
>  CREATE  UNIQUE  INDEX [IX_CommissionCodes_Code] ON
> [dbo].[CommissionCodes]([Code]) ON [PRIMARY]
> GO
>
>  CREATE  UNIQUE  INDEX [IX_ItemCodes_Code] ON [dbo].[ItemCodes]([Code]) ON
> [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Commission] ADD
>  CONSTRAINT [FK_Commission_CommissionCodes] FOREIGN KEY
>  (
>   [CommissionCodeSerialNo]
>  ) REFERENCES [dbo].[CommissionCodes] (
>   [CommissionCodeSerialNo]
>  ) ON DELETE CASCADE ,
>  CONSTRAINT [FK_Commission_ItemCodes] FOREIGN KEY
>  (
>   [ItemCodeSerialNo]
>  ) REFERENCES [dbo].[ItemCodes] (
>   [ItemCodeSerialNo]
>  ) ON DELETE CASCADE
> GO
>
>
>
>

"Brian Selzer" <br***@selzer-software.com> wrote in message
news:uCP6SiktFHA.2624@TK2MSFTNGP12.phx.gbl...
> You could remove the declarative cascading actions and code them using
> triggers.  Then you can use TRIGGER_NESTLEVEL(OBJECT_ID(N'triggerName)) in
> the trigger on Commission to determine if the trigger on CommissionCodes,
> or
> the trigger on ItemCodes initiated the delete on Commission.