• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 787
  • Last Modified:

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
0
tagtekin
Asked:
tagtekin
  • 5
  • 2
  • 2
  • +2
1 Solution
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 5
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now