Trigger to check NUlls

I would like to write a trigger that prohibits duplicates, except for nulls when sombody tyies to INSERT or UPDATE statement to create a duplicate value in the NoDupName column, it should roll back the statement and return an error message.

I created this table to test it and the following trigger but i dont get the trigger to work.

CREATE TABLE TestUniqueNulls
(RowID      int  IDENTITY  NOT NULL,
 NoDupName  varchar(20)    NULL)

create trigger TestUniqueNulls_modified
before insert or update on TestUniqueNulls
for each row
begin

IF (:NEW.TestUniqueNulls IS NULL) THEN

ROLLBACK TRANSACTION

END IF;

end;
/
show errors
tagtekinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NazermohideeenCommented:
Are you looking for the Trigger in MSSQL?
if so
Create trigger TestUniqueNulls_modified
for insert, update on TestUniqueNulls
      if (inserted.NoDupName is Null) return
      If exists (select top 1 * from TestUniqueNulls where NoDupName = inserted.noDupName) ROLLBACK TRANSACTION
end


NM
0
LowfatspreadCommented:
no quite

create trigger trgIU_TestUniqueNulls_StopDuplicates  on TestUniqueNulls
for Insert, Update

as

if exists (select Nodupname
              from TestUniqueNulls as T
             Group by Nodupname
                having count(*) > 1)
begin
         rollback transaction
end

return
0
tagtekinAuthor Commented:
I was trying to combine that in one and test it  with the select statement above?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

imran_fastCommented:
You mean like this
create trigger trgIU_TestUniqueNulls_StopDuplicates  on TestUniqueNulls
for Insert, Update

as

if exists (select Nodupname
              from TestUniqueNulls where Nodupname is not null
             Group by Nodupname
                having count(*) > 1)
begin
         rollback transaction
end

return
0
LowfatspreadCommented:
yes thanks imran
0
tagtekinAuthor Commented:
I just wanted say before I asked another question about the subject. I just want to say thank you for all of you and who ever created this site. This is the only place i can get help in my class. Not only in my database calss but all of my coputer classes. Thanksagain for all your help you are doing a great job.
0
tagtekinAuthor Commented:
[Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error

/*
CREATE TABLE TestUniqueNulls
(RowID      int  IDENTITY  NOT NULL,
 NoDupName  varchar(20)    NULL)
*/
?
create trigger trgIU_TestUniqueNulls_StopDuplicates  on TestUniqueNulls
for Insert, Update

as

if exists (select Nodupname
              from TestUniqueNulls where Nodupname is not null
             Group by Nodupname
                having count(*) > 1)
begin
        print'no dup allowed'
 rollback transaction
end

return





SET IDENTITY_INSERT TestUniqueNulls ON
INSERT TestUniqueNulls (rowid, NoDupName)
VALUES (123, 'Gates')

--select * from TestUniqueNulls
0
tagtekinAuthor Commented:
I got it to work but it still looks like allowing duplicates
/*
CREATE TABLE TestUniqueNulls
(RowID      int  IDENTITY  NOT NULL,
 NoDupName  varchar(20)    NULL)
*/
/*
create trigger trgIU_TestUniqueNulls_StopDuplicates  on TestUniqueNulls
for Insert, Update

as

if exists (select Nodupname
              from TestUniqueNulls where Nodupname is not null
             Group by Nodupname
                having count(*) > 1)
begin
        print'no dup allowed'
 rollback transaction
end

return
*/



/*
SET IDENTITY_INSERT TestUniqueNulls ON
INSERT TestUniqueNulls (rowid, NoDupName)
VALUES (123, 'Gates')
*/


select * from TestUniqueNulls
0
tagtekinAuthor Commented:
OK got it here is the solutions combined


/*
CREATE TABLE TestUniqueNulls
(RowID      int  IDENTITY  NOT NULL,
 NoDupName  varchar(20)    NULL)
*/
/*
create trigger trgIU_TestUniqueNulls_StopDuplicates  on TestUniqueNulls
for Insert, Update

as

if exists (select Nodupname
              from TestUniqueNulls where Nodupname is not null
             Group by Nodupname
                having count(*) > 1)
print('error dup name exist');

begin
         
 rollback transaction
end

return
*/



--/*
SET IDENTITY_INSERT TestUniqueNulls ON
INSERT TestUniqueNulls (rowid, NoDupName)
VALUES (123, 'Gates')
--*/

--drop trigger trgIU_TestUniqueNulls_StopDuplicates
--select * from TestUniqueNulls
0
imran_fastCommented:
you need help or u find the solution please close the quetion if so.
0
CetusMODCommented:
PAQed with points refunded (50)

CetusMOD
Community Support Moderator
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.