May 14, 2013 at 10:25 am
I have a strange requirement. This is a code issue but i am looking for a work around from daatabase perspective. I have a table that has a foreign key relation ship and it has duplicate values. Now I need a trigger to suppress inserts if a couple of column values already exists. I dont want to raise errors, i just dont want anything to get inserted if those values exist in the table.
Here is the table definition:
CREATE TABLE [dbo].[DMV1](
[DMVID] [uniqueidentifier] NOT NULL,
[FDID] [uniqueidentifier] NOT NULL,
[Value] [nvarchar](50) NOT NULL,
[LastModified] [datetime] NOT NULL,
[IsNew] [bit] NOT NULL,
CONSTRAINT [PK_DMV1] PRIMARY KEY NONCLUSTERED
(
[DMVID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DMV1] WITH CHECK ADD CONSTRAINT [FK_DMV1_FDEFs] FOREIGN KEY([FDID])
REFERENCES [dbo].[FDEFs] ([FDID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[DMV1] CHECK CONSTRAINT [FK_DMV1_FDEFs]
GO
ALTER TABLE [dbo].[DMV1] ADD DEFAULT ((0)) FOR [IsNew]
This can be prevented if i had unique constraints on the columns but unfortunately we dont and cant add now. Now the columns I am looking at are FDID, value where both are not null and FDID is a foreign key. If these columns values already exisit then i dont want their duplicates. I started writing the trigger but stuck at the logic to prevent insert:
Create Trigger [dbo].[Unique_FDID_Value] on
[dbo].[DMV1] After Insert
as
Begin
if exists (select FDid,value from inserted)
End
GO
Need your help experts. Thanks.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
May 14, 2013 at 10:50 am
Seems a bit strange that you can put a trigger on a table but not a unique constraint. The constraint will perform better and is effectively doing the same thing (expect it won't throw an exception, it will be silent). Something like this should be close.
Create Trigger [dbo].[Unique_FDID_Value] on
[dbo].[DMV1] INSTEAD OF Insert
as
if NOT exists
(
select * from inserted i
join DMV1 d on d.FDid = i.FDid and d.value = i.value
)
insert DMV1 ([ColumnsHere])
select [ColumnsHere]
from inserted
_______________________________________________________________
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/
May 14, 2013 at 11:20 am
Sean Lange (5/14/2013)
Seems a bit strange that you can put a trigger on a table but not a unique constraint. The constraint will perform better and is effectively doing the same thing (expect it won't throw an exception, it will be silent). Something like this should be close.
Create Trigger [dbo].[Unique_FDID_Value] on
[dbo].[DMV1] INSTEAD OF Insert
as
if NOT exists
(
select * from inserted i
join DMV1 d on d.FDid = i.FDid and d.value = i.value
)
insert DMV1 ([ColumnsHere])
select [ColumnsHere]
from inserted
This is my version
Create Trigger [dbo].[Unique_FdID_Value] on
[dbo].[DMV1] INSTEAD OF INSERT
as
Begin
IF (NOT EXISTS (SELECT dfv.fdid,dfv.value
FROM DMV1 dfv, inserted I
WHERE dfv.fdid = I.fdid and dfv.value=i.value))
INSERT INTO DMV1
SELECT top1* FROM inserted
else
Print "can't insert duplicate"
End
Do you think it works? The reason I do top 1* is the inserts can be from batch...and I want the most recent front hat bunch of values
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
May 14, 2013 at 12:19 pm
Sapen (5/14/2013)
Sean Lange (5/14/2013)
Seems a bit strange that you can put a trigger on a table but not a unique constraint. The constraint will perform better and is effectively doing the same thing (expect it won't throw an exception, it will be silent). Something like this should be close.
Create Trigger [dbo].[Unique_FDID_Value] on
[dbo].[DMV1] INSTEAD OF Insert
as
if NOT exists
(
select * from inserted i
join DMV1 d on d.FDid = i.FDid and d.value = i.value
)
insert DMV1 ([ColumnsHere])
select [ColumnsHere]
from inserted
This is my version
Create Trigger [dbo].[Unique_FdID_Value] on
[dbo].[DMV1] INSTEAD OF INSERT
as
Begin
IF (NOT EXISTS (SELECT dfv.fdid,dfv.value
FROM DMV1 dfv, inserted I
WHERE dfv.fdid = I.fdid and dfv.value=i.value))
INSERT INTO DMV1
SELECT top1* FROM inserted
else
Print "can't insert duplicate"
End
Do you think it works? The reason I do top 1* is the inserts can be from batch...and I want the most recent front hat bunch of values
You have a few issues here, and pointed out 1 in my code. First of all you should not use the old style joins and instead use the ansi-92 style joins like I presented. They are far less error prone. Your code does not support multi-row inserts (neither does mine). Well mine will sort of work. Then you have a top with no order by.
Here is my take on what this should look like.
Create Trigger [dbo].[Unique_FdID_Value] on [dbo].[DMV1] INSTEAD OF INSERT as
IF (NOT EXISTS (
SELECT * --no need to check for individual colums inside an EXISTS
FROM DMV1 dfv
join inserted I on dfv.fdid = I.fdid and dfv.value=i.value
)
)
INSERT INTO DMV1
SELECT * FROM inserted i
where not exists
(
select dfv.fdid, dfv.value
from DMV1 dfv
where dfv.value = i.value
and dfv.fdid = i.value
)
else
Print 'can''t insert duplicate'
Not really sure what you meant about using top 1 to only get the most recent. If you add a top 1 to this are you certain that nobody will EVER attempt to insert two legitimate values? And if you use top 1 in ANY query you MUST also use an order by or you have no idea which is the top 1.
_______________________________________________________________
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/
May 14, 2013 at 1:41 pm
Sean Lange (5/14/2013)
Seems a bit strange that you can put a trigger on a table but not a unique constraint. The constraint will perform better and is effectively doing the same thing (expect it won't throw an exception, it will be silent). Something like this should be close.
Create Trigger [dbo].[Unique_FDID_Value] on
[dbo].[DMV1] INSTEAD OF Insert
as
if NOT exists
(
select * from inserted i
join DMV1 d on d.FDid = i.FDid and d.value = i.value
)
insert DMV1 ([ColumnsHere])
select [ColumnsHere]
from inserted
I'd probably skip the IF NOT EXISTS and just do this in the trigger (INSTEAD OF)
Insert into DMV1
select * from inserted I where NOT EXISTS(SELECT 1 FROM DMV1 d WHERE d.FDid = i.FDid and d.value = i.value)
This should only insert rows where the values don't exist already.
I'd think you'd also want to do something similar with UPDATEs
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 14, 2013 at 1:43 pm
Sean Lange (5/14/2013)
Sapen (5/14/2013)
Sean Lange (5/14/2013)
Seems a bit strange that you can put a trigger on a table but not a unique constraint. The constraint will perform better and is effectively doing the same thing (expect it won't throw an exception, it will be silent). Something like this should be close.
Create Trigger [dbo].[Unique_FDID_Value] on
[dbo].[DMV1] INSTEAD OF Insert
as
if NOT exists
(
select * from inserted i
join DMV1 d on d.FDid = i.FDid and d.value = i.value
)
insert DMV1 ([ColumnsHere])
select [ColumnsHere]
from inserted
This is my version
Create Trigger [dbo].[Unique_FdID_Value] on
[dbo].[DMV1] INSTEAD OF INSERT
as
Begin
IF (NOT EXISTS (SELECT dfv.fdid,dfv.value
FROM DMV1 dfv, inserted I
WHERE dfv.fdid = I.fdid and dfv.value=i.value))
INSERT INTO DMV1
SELECT top1* FROM inserted
else
Print "can't insert duplicate"
End
Do you think it works? The reason I do top 1* is the inserts can be from batch...and I want the most recent front hat bunch of values
You have a few issues here, and pointed out 1 in my code. First of all you should not use the old style joins and instead use the ansi-92 style joins like I presented. They are far less error prone. Your code does not support multi-row inserts (neither does mine). Well mine will sort of work. Then you have a top with no order by.
Here is my take on what this should look like.
Create Trigger [dbo].[Unique_FdID_Value] on [dbo].[DMV1] INSTEAD OF INSERT as
IF (NOT EXISTS (
SELECT * --no need to check for individual colums inside an EXISTS
FROM DMV1 dfv
join inserted I on dfv.fdid = I.fdid and dfv.value=i.value
)
)
INSERT INTO DMV1
SELECT * FROM inserted i
where not exists
(
select dfv.fdid, dfv.value
from DMV1 dfv
where dfv.value = i.value
and dfv.fdid = i.value
)
else
Print 'can''t insert duplicate'
Not really sure what you meant about using top 1 to only get the most recent. If you add a top 1 to this are you certain that nobody will EVER attempt to insert two legitimate values? And if you use top 1 in ANY query you MUST also use an order by or you have no idea which is the top 1.
Sean,
your new trigger won't necessarily work with a batch insert either. As currently written if one row in the batch exists none are inserted. This could be the desired behavior.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 14, 2013 at 1:52 pm
Jack Corbett (5/14/2013)
Sean Lange (5/14/2013)
Seems a bit strange that you can put a trigger on a table but not a unique constraint. The constraint will perform better and is effectively doing the same thing (expect it won't throw an exception, it will be silent). Something like this should be close.
Create Trigger [dbo].[Unique_FDID_Value] on
[dbo].[DMV1] INSTEAD OF Insert
as
if NOT exists
(
select * from inserted i
join DMV1 d on d.FDid = i.FDid and d.value = i.value
)
insert DMV1 ([ColumnsHere])
select [ColumnsHere]
from inserted
I'd probably skip the IF NOT EXISTS and just do this in the trigger (INSTEAD OF)
Insert into DMV1
select * from inserted I where NOT EXISTS(SELECT 1 FROM DMV1 d WHERE d.FDid = i.FDid and d.value = i.value)
This should only insert rows where the values don't exist already.
I'd think you'd also want to do something similar with UPDATEs
That is pretty much the code I posted in my most recent post. The one difference is the OP wanted to print a message. The far bigger issue with my original code was that it did not handle multiple row inserts correctly. The initial if exists was ok but the following insert would have foolishly inserted the duplicates that it discovered initially.
_______________________________________________________________
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/
May 14, 2013 at 1:53 pm
Jack Corbett (5/14/2013)
Sean Lange (5/14/2013)
Sapen (5/14/2013)
Sean Lange (5/14/2013)
Seems a bit strange that you can put a trigger on a table but not a unique constraint. The constraint will perform better and is effectively doing the same thing (expect it won't throw an exception, it will be silent). Something like this should be close.
Create Trigger [dbo].[Unique_FDID_Value] on
[dbo].[DMV1] INSTEAD OF Insert
as
if NOT exists
(
select * from inserted i
join DMV1 d on d.FDid = i.FDid and d.value = i.value
)
insert DMV1 ([ColumnsHere])
select [ColumnsHere]
from inserted
This is my version
Create Trigger [dbo].[Unique_FdID_Value] on
[dbo].[DMV1] INSTEAD OF INSERT
as
Begin
IF (NOT EXISTS (SELECT dfv.fdid,dfv.value
FROM DMV1 dfv, inserted I
WHERE dfv.fdid = I.fdid and dfv.value=i.value))
INSERT INTO DMV1
SELECT top1* FROM inserted
else
Print "can't insert duplicate"
End
Do you think it works? The reason I do top 1* is the inserts can be from batch...and I want the most recent front hat bunch of values
You have a few issues here, and pointed out 1 in my code. First of all you should not use the old style joins and instead use the ansi-92 style joins like I presented. They are far less error prone. Your code does not support multi-row inserts (neither does mine). Well mine will sort of work. Then you have a top with no order by.
Here is my take on what this should look like.
Create Trigger [dbo].[Unique_FdID_Value] on [dbo].[DMV1] INSTEAD OF INSERT as
IF (NOT EXISTS (
SELECT * --no need to check for individual colums inside an EXISTS
FROM DMV1 dfv
join inserted I on dfv.fdid = I.fdid and dfv.value=i.value
)
)
INSERT INTO DMV1
SELECT * FROM inserted i
where not exists
(
select dfv.fdid, dfv.value
from DMV1 dfv
where dfv.value = i.value
and dfv.fdid = i.value
)
else
Print 'can''t insert duplicate'
Not really sure what you meant about using top 1 to only get the most recent. If you add a top 1 to this are you certain that nobody will EVER attempt to insert two legitimate values? And if you use top 1 in ANY query you MUST also use an order by or you have no idea which is the top 1.
Sean,
your new trigger won't necessarily work with a batch insert either. As currently written if one row in the batch exists none are inserted. This could be the desired behavior.
True, I wasn't really sure what the intention was though. Kind of hard to tell with the message in there which causes some confusion.
_______________________________________________________________
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/
May 14, 2013 at 2:31 pm
Sapen,
What do you want to happen if there is a batch/multi-row insert and some of the rows should be inserted but some should not?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 14, 2013 at 9:39 pm
I think I was able to figure this out..
http://mssqlnuggets.wordpress.com/
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply