How to force unique entries in a linking table?

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 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

How to find the last updated tables in a database

Hi,

I am trying to create a data dictionary for a huge application which has aroung 300 tables in the database....when i perform any operation in the application some tables are updated.... can you help me to find out how can we find out the last updated tables in the database ?

Try using SQL Profiler to monitor which tables are being updated.

You can also search for SQL Dependency Analyzer - there are a few tools out there that will track this.

cheers,

Andrew

How to find a lower date

I have following problem:

table includes times for startup and end of operation as datetime field
related to daily shift operations:

dateid date starttime endtime

458 2006-12-29 22:00 23:15
458 2006-12-29 00:15 01:30
459 2006-12-30 20:00 21:10
459 2006-12-30 22:15 23:35
459 2006-12-30 23:30 00:40
459 2006-12-30 01:50 02:30

records are inserted for a date related to begining of the shift, although
some operations are performed also past the midnight (actualy next day, ex:
2006-12-31), but belongs to same shift (group)

Now I need to build a function that corrects (updates) the date of every
operation recorded after midnight to a date+1 value, so all records related
to same groups (458, 459, etc) that starts after midnight has correct date.

The procedure has to update already exiting table.

Any solution?
GreyI'm a bit confused. You mention that it uses a datetime data type,
but the data shows appears to be a column with a date and no time, and
two columns with time but no date. Datetime always has both.

Roy Harvey
Beacon Falls, CT

On Thu, 4 Jan 2007 00:26:39 +0100, "Grey" <null@.null.nullwrote:

Quote:

Originally Posted by

>I have following problem:
>
>table includes times for startup and end of operation as datetime field
>related to daily shift operations:
>
>dateid date starttime endtime
>
>458 2006-12-29 22:00 23:15
>458 2006-12-29 00:15 01:30
>459 2006-12-30 20:00 21:10
>459 2006-12-30 22:15 23:35
>459 2006-12-30 23:30 00:40
>459 2006-12-30 01:50 02:30
>
>records are inserted for a date related to begining of the shift, although
>some operations are performed also past the midnight (actualy next day, ex:
>2006-12-31), but belongs to same shift (group)
>
>Now I need to build a function that corrects (updates) the date of every
>operation recorded after midnight to a date+1 value, so all records related
>to same groups (458, 459, etc) that starts after midnight has correct date.
>
>The procedure has to update already exiting table.
>
>Any solution?
>Grey
>

|||"Grey" <null@.null.nullwrote in message
news:enhe3f$ura$1@.news.dialog.net.pl...

Quote:

Originally Posted by

>I have following problem:
>
table includes times for startup and end of operation as datetime field
related to daily shift operations:
>
dateid date starttime endtime
>
458 2006-12-29 22:00 23:15
458 2006-12-29 00:15 01:30
459 2006-12-30 20:00 21:10
459 2006-12-30 22:15 23:35
459 2006-12-30 23:30 00:40
459 2006-12-30 01:50 02:30
>
records are inserted for a date related to begining of the shift, although
some operations are performed also past the midnight (actualy next day,
ex: 2006-12-31), but belongs to same shift (group)
>
Now I need to build a function that corrects (updates) the date of every
operation recorded after midnight to a date+1 value, so all records
related to same groups (458, 459, etc) that starts after midnight has
correct date.
>
The procedure has to update already exiting table.

Assuming no operation will exceed 24 hours it might look something like
this:

SELECT dateid,
date as startdate,
starttime,
CASE
WHEN endtime starttime
THEN convert(datetime,date)
ELSE convert(datetime,date)+ 1
END AS EndDate,
endtime
FROM Table1

Quote:

Originally Posted by

>
Any solution?
Grey
>
>

|||

Quote:

Originally Posted by

I'm a bit confused. You mention that it uses a datetime data type,
but the data shows appears to be a column with a date and no time, and
two columns with time but no date. Datetime always has both.
>
Roy Harvey
Beacon Falls, CT

The fields are presented for simplicity. they have both date and time, but
the front end adds only time, so the datepart of the filed is disregarded.
Any way it contains a wrong value for operations past midnight.

Grey|||

Quote:

Originally Posted by

Assuming no operation will exceed 24 hours it might look something like
this:
>
SELECT dateid,
date as startdate,
starttime,
CASE
WHEN endtime starttime
THEN convert(datetime,date)
ELSE convert(datetime,date)+ 1
END AS EndDate,
endtime
FROM Table1

Thats too easy. Table contains values from serveral hundreds of days. each
day has an id dateid like 458, 459, etc.
I have to go trough all table finding times past midninght for given group
and change the date to correct one.

Grey

Quote:

Originally Posted by

>
>
>

Quote:

Originally Posted by

>>
>Any solution?
>Grey
>>
>>

>
>

|||OK, let me see if I understand this. Have I identified the row that
need fixing correctly? I chose the one row that ended before it
started.

Quote:

Originally Posted by

>dateid date starttime endtime
>
>458 2006-12-29 22:00 23:15
>458 2006-12-29 00:15 01:30
>459 2006-12-30 20:00 21:10
>459 2006-12-30 22:15 23:35
>459 2006-12-30 23:30 00:40 --Problem?
>459 2006-12-30 01:50 02:30

If that is not correct, please mark which ones have the problem and
what rule was made it so.

Roy Harvey
Beacon Falls, CT

On Thu, 4 Jan 2007 14:47:28 +0100, "Grey" <null@.null.nullwrote:

Quote:

Originally Posted by

>

Quote:

Originally Posted by

>I'm a bit confused. You mention that it uses a datetime data type,
>but the data shows appears to be a column with a date and no time, and
>two columns with time but no date. Datetime always has both.
>>
>Roy Harvey
>Beacon Falls, CT

>
>The fields are presented for simplicity. they have both date and time, but
>the front end adds only time, so the datepart of the filed is disregarded.
>Any way it contains a wrong value for operations past midnight.
>
>Grey
>

|||

Quote:

Originally Posted by

Quote:

Originally Posted by

>>dateid date starttime endtime
>>
>>458 2006-12-29 22:00 23:15
>>458 2006-12-29 00:15 01:30 --Problem?

Quote:

Originally Posted by

Quote:

Originally Posted by

>>459 2006-12-30 20:00 21:10
>>459 2006-12-30 22:15 23:35
>>459 2006-12-30 23:30 00:40
>>459 2006-12-30 01:50 02:30 --Problem?
>>459 2006-12-30 03:30 04:30 --Problem?

The start time is relevant - marked records should have a date + 1

Grey|||Something like this might do what you want.

First the simple way, with two seperate UPDATEs, one for each column:

UPDATE Operations
SET starttime = DATEADD(day,1,starttime)
WHERE datepart(hour,starttime) < 12

UPDATE Operations
SET endtime = DATEADD(day,1,endtime)
WHERE datepart(hour,endtime) < 12

Note that I chose hour 12 as an arbitrary point in the day such that
times before that are "after midnight" and times after that are
"before midnight". Choose whatever time you prefer.

Also note that this "fixes" (or destroys, you tell me) the second date
in the sample row:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>>459 2006-12-30 23:30 00:40

This was not designated as a problem row, but I thought the second
date would be. Please clarify if this second date should not be
changed.

Anyway, the two-UPDATE approach is innefficient, as most of the
changes happen to both columns on the same row. So we combine them,
but it becomes rather more complicated.

UPDATE Operations
SET starttime = CASE WHEN datepart(hour,starttime) < 12
THEN DATEADD(day,1,starttime)
ELSE starttime
END,
endtime = CASE WHEN datepart(hour,endtime) < 12
THEN DATEADD(day,1,endtime)
ELSE starttime
END
WHERE datepart(hour,starttime) < 12
OR datepart(hour,endtime) < 12

Hope that helps.

Roy Harvey
Beacon Falls, CT

On Thu, 4 Jan 2007 16:17:13 +0100, "Grey" <null@.null.nullwrote:

Quote:

Originally Posted by

>

Quote:

Originally Posted by

Quote:

Originally Posted by

>>>dateid date starttime endtime
>>>
>>>458 2006-12-29 22:00 23:15
>>>458 2006-12-29 00:15 01:30 --Problem?

>

Quote:

Originally Posted by

Quote:

Originally Posted by

>>>459 2006-12-30 20:00 21:10
>>>459 2006-12-30 22:15 23:35
>>>459 2006-12-30 23:30 00:40
>>>459 2006-12-30 01:50 02:30 --Problem?
>>>459 2006-12-30 03:30 04:30 --Problem?

>
>The start time is relevant - marked records should have a date + 1
>
>Grey
>

|||OK, now I have solved the problem by an approach I found at some other post
presented by Joe Celko:

CREATE TABLE Foobar

(id INTEGER NOT NULL PRIMARY KEY,

name CHAR(10) NOT NULL);

INSERT INTO Foobar VALUES (12, 'Johnson');

INSERT INTO Foobar VALUES (57, 'Nelson');

INSERT INTO Foobar VALUES (11, 'Roberts');

INSERT INTO Foobar VALUES (68, 'Smith');

SELECT F1.id, F1.name,

(SELECT MAX (id)

FROM Foobar AS F2

WHERE F2.id < F1.id) AS prev_id,

(SELECT MIN(id)

FROM Foobar AS F3

WHERE F3.id F1.id) AS next_id

FROM Foobar AS F1;

These subquery expressions are the LUB (least upper
bound) and the GLB (greatest lower bound).

Thats finds the lower records. Than a simply comparation like this:

SELECT CASE WHEN f1.id< (select id from foobar WHERE id =
dbo.get_prev_id(F1.id)) THEN date+1 ELSE date END AS date_past_midnight from
foobar as f1

Function get_prev_id is like:

SELECT (SELECT MAX (id) FROM foobar AS F2 WHERE F2.id < F1.id) AS prev_id

FROM foobar AS F1

where F1.id = @.id <-- (argument passed to function)

Thanks for all help

GREY