Triggers

Question

Triggers

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

You are certainly pretty close. Here is a small little bit of help for working with triggers. They can be pretty difficult to debug because you can't just run it like a query. In order to see what is happening in your trigger you need to add some debugging code to help figure out what is going on.

Try adding the following code right before your IF statement in the trigger:

SELECT Count(Distinct A.NoDupName) as DistinctCount

FROM TestUniqueNulls AS A

INNER JOIN Inserted AS B ON A.NoDupName = B.noDupName

It seems that you are never raising the error. The above code is the test inside your if.

_______________________________________________________________

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/

joshphillips7145 Right there with Babe Points: 720 More actions · Answer 2

So I changed it to a Instead Of trigger and it now prevents any entries from being inserted but no error, I just cannot figure out why!!:sick:

Create Trigger NoDuplicates

On TestUniqueNulls

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

joshphillips7145 (8/8/2012)
So I changed it to a Instead Of trigger and it now prevents any entries from being inserted but no error, I just cannot figure out why!!:sick:

You changed it what kind??? The name should be a hint as to what that means. 😉

_______________________________________________________________

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/

joshphillips7145 Right there with Babe Points: 720 More actions · Answer 4

Ok so I switched it back to an after trigger and changed up my join but still cannot get it right, I know it has to be very close at least I think it is what should I try?

Create Trigger NoDuplicates

On TestUniqueNulls

After Insert, Update AS

Begin

IF

(Select Count(Distinct A.NoDupName)

From Inserted AS A Inner join TestUniqueNulls AS B

On A.NoDupName = B.noDupName) > 1

Begin

RollBack Tran

RaisError ('Duplicate value', 11, 1)

End

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

I realize that the idea is to write a trigger to accomplish allowing multiple nulls but a single unique non null value, however, this is SQL Server 2008 so why not the following?

CREATE TABLE dbo.TestUniqueNulls(

RowID int IDENTITY NOT NULL,

NoDupName varchar(20) NULL

);

go

CREATE UNIQUE NONCLUSTERED INDEX UK_NoDupName ON dbo.TestUniqueNulls (NoDupName)

WHERE NoDupName IS NOT NULL;

go

SELECT * FROM dbo.TestUniqueNulls;

go

INSERT INTO dbo.TestUniqueNulls (NoDupName)

VALUES ('Josh');

GO

SELECT * FROM dbo.TestUniqueNulls;

go

INSERT INTO dbo.TestUniqueNulls (NoDupName)

VALUES ('Josh');

GO

SELECT * FROM dbo.TestUniqueNulls;

go

DROP TABLE dbo.TestUniqueNulls;

GO

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

joshphillips7145 (8/8/2012)
Ok so I switched it back to an after trigger and changed up my join but still cannot get it right, I know it has to be very close at least I think it is what should I try?
Create Trigger NoDuplicates
On TestUniqueNulls
After Insert, Update AS
Begin
IF
(Select Count(Distinct A.NoDupName)
From Inserted AS A Inner join TestUniqueNulls AS B
On A.NoDupName = B.noDupName) > 1
Begin
RollBack Tran
RaisError ('Duplicate value', 11, 1)
End
End

So did you figure out why the INSTEAD OF trigger was not inserting data?

It looks like in the above all you did was change the order of Inserted and TestUniqueNulls? Did you try adding the debug code I suggested? You should, it should enlighten you to the reason this isn't quite right.

_______________________________________________________________

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/

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

Lynn Pettis (8/8/2012)
I realize that the idea is to write a trigger to accomplish allowing multiple nulls but a single unique non null value, however, this is SQL Server 2008 so why not the following?

Pretty sure this is homework and I doubt the professor would allow what in the real world is a better approach to the problem. 😉 I would of course agree that a unique index would be far better suited to the task at hand.

_______________________________________________________________

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/

joshphillips7145 Right there with Babe Points: 720 More actions · Answer 8

Oh this does exactly what I need it to do, however I must use a trigger here.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

I have figured out what the problem is, have you?

Question, what event should cause the trigger to rollback the insert or update.

Use a temp table and simulate what should happen in the trigger for an insert or update to determine if there is a duplicate value.

By the way, I have a working trigger. I don't use them often (meaning very rarely) so it took me a bit working with yours to make it work.

joshphillips7145 Right there with Babe Points: 720 More actions · Answer 10

The event that should cause it to rollback would be finding a duplicate entry right?

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

joshphillips7145 (8/8/2012)
The event that should cause it to rollback would be finding a duplicate entry right?

Have you used the debug code I posted several posts ago and keep asking if you have looked at it? It should be painfully obvious that your code is never making it inside your IF condition right? That means you should do everything you can to figure out WHY it isn't making it inside. One distinct possibility of debugging this is to use the debug I gave you. Then evaluate the output when attempting to insert duplicates. Give it a try.

_______________________________________________________________

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/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

joshphillips7145 (8/8/2012)
The event that should cause it to rollback would be finding a duplicate entry right?

Yes, but how do you know you have a duplicate value in an insert ot update batch?

Create your table with no triggers.

insert a single record into it.

now create a temp table and insert the same record into it.

Now write a query that would provide you with the information you think you need to know you have a duplicate record. Hint, the data is inserted into your table before the trigger is fired. (Hmm, I may have another solution since your table has an identity column, but ignore that for now).

joshphillips7145 Right there with Babe Points: 720 More actions · Answer 13

I did use it, however didn't really think about it like you said

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 14

--So completely broke the discussion thread as I didn't see the page count. My apologies.

Don

joshphillips7145 Right there with Babe Points: 720 More actions · Answer 15

Your help has been very much appreciated! I still have not gotten the answer but have and appt. so perhaps if you are on later I will update my code. Thanks again!