August 8, 2012 at 8:06 am
A column that accepts null values but has a unique constraint can only have a single row with a null value. Write a trigger that prohibits duplicates, except for nulls. If an INSERT or UPDATE statement creates a duplicate value in the NoDupName column, roll back the statement and return an error message.
This is my table, I just don't really have a clue how to start from here!!
CREATE TABLE TestUniqueNulls
(RowID int IDENTITY NOT NULL,
NoDupName varchar(20) NULL)
August 8, 2012 at 8:10 am
This sounds a lot like homework. As such you will find people here willing to help guide you but not many that will provide you with code. The reason is that if you are spoonfed the solution you don't learn anything.
Given what your professor has talked about where do you think you might begin? Not necessarily code but logically.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 8, 2012 at 8:23 am
Maybe something like this:
Create Trigger NoDuplicates
/*Code
After Insert Update AS
Begin
IF
(Select Count(*)
From TestUniqueNulls
*/
The thing is I think there needs to be a join but the question doesn't specify enough information at least if it does I am missing it.
August 8, 2012 at 8:28 am
think it through...how does a simple count() tell you whether there are duplicates?
I'd don't think you have teh test down yet.
are you familiar enough with SQL triggers that you know about the virtual tables INSERTED and DELETED that exist only for the duration of the trigger?
the INSERTED table will have the "new" values.
how would you compare that virtual table to the current values in the table?
(that's your hint...can you show us some code?)
Lowell
August 8, 2012 at 8:49 am
Create Trigger NoDuplicates
On TestUniqueNulls
After Insert, Update AS
Begin
IF
(Select Count(*)
From TestUniqueNulls join TestuniqueNulls
On TestUniqueNulls.NoDupName = TestUniqueNulls.noDupName) > 1
Begin
RollBack Tran
RaiseError('Duplicate value', 11, 1)
End
End
August 8, 2012 at 8:52 am
joshphillips7145 (8/8/2012)
Create Trigger NoDuplicatesOn TestUniqueNulls
After Insert, Update AS
Begin
IF
(Select Count(*)
From TestUniqueNulls join TestuniqueNulls
On TestUniqueNulls.NoDupName = TestUniqueNulls.noDupName) > 1
Begin
RollBack Tran
RaiseError('Duplicate value', 11, 1)
End
End
That is reasonably close. Take a look at the hint Lowell suggested about the virtual tables inserted and deleted. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 8, 2012 at 8:54 am
I Think this may be right now What would I do to test it? Write a select Statement?
Create Trigger NoDuplicates
On TestUniqueNulls
After Insert, Update AS
Begin
IF
(Select Count(*)
From TestUniqueNulls AS A join TestuniqueNulls AS B
On A.NoDupName = B.noDupName) > 1
Begin
RollBack Tran
RaisError ('Duplicate value', 11, 1)
End
End
August 8, 2012 at 8:57 am
You still need to look closer at the inserted and deleted virtual tables.
joshphillips7145 (8/8/2012)
I Think this may be right now What would I do to test it? Write a select Statement?
When does the trigger fire? Does it fire when you select data from this table?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 8, 2012 at 9:00 am
a nice first effort!
i think the issues still outstanding are as follows:
i think you are counting all rows, not names:
it's like counting the students in calss(ie 31) but not how many are named "josh"
you are not comparing the inserted rows to the whole list...you are kind of comparing the list against itself.
a join of a table agaisnt itself gives a "cartesian" product...if the table has ten rows, then 10x10 = 100, and that's how many
rows would be in your results.
add a handful of rows to your table, what happens?
Lowell
August 8, 2012 at 9:36 am
Ok so I added 'distinct noDupName' to my count but i am still not getting it.
Create Trigger NoDuplicates
On TestUniqueNulls
After Insert, Update AS
Begin
IF
(Select Count(Distinct NoDupName)
From TestUniqueNulls AS A join TestuniqueNulls AS B
On TestUniqueNulls.NoDupName = TestUniqueNulls.noDupName) > 1
Begin
RollBack Tran
RaisError ('Duplicate value', 11, 1)
End
End
August 8, 2012 at 9:40 am
ok, i corrected your alias problems, but not the logic.
here's the test code; try it, and see the error you get when inserting the second row:
CREATE TABLE TestUniqueNulls
(RowID int IDENTITY NOT NULL,
NoDupName varchar(20) NULL)
GO
Create Trigger NoDuplicates
On TestUniqueNulls
After Insert, Update AS
Begin
IF
(Select Count(Distinct A.NoDupName)
From TestUniqueNulls AS A join TestuniqueNulls AS B
On A.NoDupName = B.noDupName) > 1
Begin
RollBack Tran
RaisError ('Duplicate value', 11, 1)
End
End
GO
INSERT INTO TestUniqueNulls(NoDupName)
SELECT 'josh'
INSERT INTO TestUniqueNulls(NoDupName)
SELECT 'Sean'
Lowell
August 8, 2012 at 10:30 am
Good task! It has some not so obvious pitfalls, and not so easy as it may seem from the first look, imho.
Here is my notes about what should be taken into consideration when implementing this:
1) duplicates might be not only after adding name that is already in the table, but adding duplicates at once - insert... values ('dup'),('dup').
2) as we have the same trigger for update we should take into consideration cross update. for example we have 2 rows id1='a', id2='b', now we change places in single statement, id1='b', id2='a' - this is a valid update and should not produce error.
3) for example our trigger was disabled for some reasons, or was created later, after the table already had data. So just counting any duplicates (not only those which are relative to modification) we may reject a valid update or insert.
The goal is to enforse all these rules at once. There is a solution, the clue might be to think in the way that
- how to get rows as this updae/insert/merge was already performed (inserted+union+main table)
- how to check duplicates for some field in a set of rows (exists+group by + count + having)
Good luck!
p.s.
In the real world there is fourth rule
4) It should be quick =)
That's why it is much more preferable to use filtered constraint.
August 8, 2012 at 11:42 am
Thanks for your help, Check this out and see if I finally got it!
Create Trigger NoDuplicates
On TestUniqueNulls
After Insert, Update AS
Begin
IF
(Select Count(Distinct A.NoDupName)
From TestUniqueNulls AS A join Inserted AS B
On A.NoDupName = B.noDupName) > 1
Begin
RollBack Tran
RaisError ('Duplicate value', 11, 1)
End
End
August 8, 2012 at 11:44 am
nope that didn't work either now it let's me insert data but it is also letting me use duplicates
August 8, 2012 at 11:48 am
testing testing testing.
that's the key.
here's your updated script; i can insert 'josh' multiple times.
DROP TABLE TestUniqueNulls
CREATE TABLE TestUniqueNulls
(RowID int IDENTITY NOT NULL,
NoDupName varchar(20) NULL)
GO
Create Trigger NoDuplicates
On TestUniqueNulls
After Insert, Update AS
Begin
IF
(Select Count(Distinct A.NoDupName)
From TestUniqueNulls AS A join Inserted AS B
On A.NoDupName = B.noDupName) > 1
Begin
RollBack Tran
RaisError ('Duplicate value', 11, 1)
End
End
GO
INSERT INTO TestUniqueNulls(NoDupName)
SELECT 'josh'
INSERT INTO TestUniqueNulls(NoDupName)
SELECT 'Sean'
--these should ERROR, right?
INSERT INTO TestUniqueNulls(NoDupName)
SELECT 'josh'
INSERT INTO TestUniqueNulls(NoDupName)
SELECT 'Sean'
Lowell
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply