between the 'Group' table and the 'Operation' table (each group is has
permission to perform one or more of the available operations)
PROBLEM
=======
I need to prevent duplicate entries being created. e.g. lets say that
in the 'Group2Operation' table a record links the 'editor' group to
the 'publish' operation. Should I prevent an administrator creating a
duplicate of that record? (Otherwise deleting that permission will
have to be done twice or more for it to be effective)
SOLUTION?
=========
So far I've done this with a trigger:
CREATE TRIGGER Group2OperationDuplicates ON dbo.Group2Operation
FOR INSERT, UPDATE
AS UPDATE Group2Operation
SET NoDuplicate = CONVERT(nvarchar(10),GroupID) + OperationTag
The 'NoDuplicate' unique index column in the Group2Operation table
stores a concatenation of the unique group and operation identifiers.
So when an attempt is made to create a record, the trigger is fired.
If there is a duplicate, this will mean a duplicate entry in the
'NoDuplicate' column. As a result, the INSERT or UPDATE will fail and
the duplication will be prevented.
WHAT DO YOU THINK?
==================
What do you think? Am I going about this in the right way? Is a
trigger a good way to do this or should I rely on application logic to
prevent duplicates?
Any help appreciated by this db novice.
John GristConsider creating a PRIMARY KEY or UNIQUE constraint on the Group2Operation
table. This will ensure duplicate entries cannot be inserted. There is no
need to concatenate values or use a separate table since you can specify a
composite key:
ALTER TABLE Group2Operation
ADD CONSTRAINT PK_Group2Operation
PRIMARY KEY (GroupID, OperationTag)
BTW, it's a good practice to post DDL (create table) when posting questions
to this forum.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"grist2mill" <grist2mill@.excite.com> wrote in message
news:46e240e.0409030059.79f2f36d@.posting.google.co m...
>I have a table 'Group2Operation' that stores many to many relations
> between the 'Group' table and the 'Operation' table (each group is has
> permission to perform one or more of the available operations)
> PROBLEM
> =======
> I need to prevent duplicate entries being created. e.g. lets say that
> in the 'Group2Operation' table a record links the 'editor' group to
> the 'publish' operation. Should I prevent an administrator creating a
> duplicate of that record? (Otherwise deleting that permission will
> have to be done twice or more for it to be effective)
> SOLUTION?
> =========
> So far I've done this with a trigger:
> CREATE TRIGGER Group2OperationDuplicates ON dbo.Group2Operation
> FOR INSERT, UPDATE
> AS UPDATE Group2Operation
> SET NoDuplicate = CONVERT(nvarchar(10),GroupID) + OperationTag
> The 'NoDuplicate' unique index column in the Group2Operation table
> stores a concatenation of the unique group and operation identifiers.
> So when an attempt is made to create a record, the trigger is fired.
> If there is a duplicate, this will mean a duplicate entry in the
> 'NoDuplicate' column. As a result, the INSERT or UPDATE will fail and
> the duplication will be prevented.
> WHAT DO YOU THINK?
> ==================
> What do you think? Am I going about this in the right way? Is a
> trigger a good way to do this or should I rely on application logic to
> prevent duplicates?
> Any help appreciated by this db novice.
> John Grist|||That looks much better. I though there ought to be a better way.
Many thanks for your help
John
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message news:<uh_Zc.14996$lu3.12768@.newssvr24.news.prodigy.com>...
> Consider creating a PRIMARY KEY or UNIQUE constraint on the Group2Operation
> table. This will ensure duplicate entries cannot be inserted. There is no
> need to concatenate values or use a separate table since you can specify a
> composite key:
> ALTER TABLE Group2Operation
> ADD CONSTRAINT PK_Group2Operation
> PRIMARY KEY (GroupID, OperationTag)
> BTW, it's a good practice to post DDL (create table) when posting questions
> to this forum.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "grist2mill" <grist2mill@.excite.com> wrote in message
> news:46e240e.0409030059.79f2f36d@.posting.google.co m...
> >I have a table 'Group2Operation' that stores many to many relations
> > between the 'Group' table and the 'Operation' table (each group is has
> > permission to perform one or more of the available operations)
> > PROBLEM
> > =======
> > I need to prevent duplicate entries being created. e.g. lets say that
> > in the 'Group2Operation' table a record links the 'editor' group to
> > the 'publish' operation. Should I prevent an administrator creating a
> > duplicate of that record? (Otherwise deleting that permission will
> > have to be done twice or more for it to be effective)
> > SOLUTION?
> > =========
> > So far I've done this with a trigger:
> > CREATE TRIGGER Group2OperationDuplicates ON dbo.Group2Operation
> > FOR INSERT, UPDATE
> > AS UPDATE Group2Operation
> > SET NoDuplicate = CONVERT(nvarchar(10),GroupID) + OperationTag
> > The 'NoDuplicate' unique index column in the Group2Operation table
> > stores a concatenation of the unique group and operation identifiers.
> > So when an attempt is made to create a record, the trigger is fired.
> > If there is a duplicate, this will mean a duplicate entry in the
> > 'NoDuplicate' column. As a result, the INSERT or UPDATE will fail and
> > the duplication will be prevented.
> > WHAT DO YOU THINK?
> > ==================
> > What do you think? Am I going about this in the right way? Is a
> > trigger a good way to do this or should I rely on application logic to
> > prevent duplicates?
> > Any help appreciated by this db novice.
> > John Grist
