tagtekin
asked on
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
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
no quite
create trigger trgIU_TestUniqueNulls_Stop
for Insert, Update
as
if exists (select Nodupname
from TestUniqueNulls as T
Group by Nodupname
having count(*) > 1)
begin
rollback transaction
end
return
create trigger trgIU_TestUniqueNulls_Stop
for Insert, Update
as
if exists (select Nodupname
from TestUniqueNulls as T
Group by Nodupname
having count(*) > 1)
begin
rollback transaction
end
return
ASKER
I was trying to combine that in one and test it with the select statement above?
You mean like this
create trigger trgIU_TestUniqueNulls_Stop
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
create trigger trgIU_TestUniqueNulls_Stop
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
yes thanks imran
ASKER
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.
ASKER
[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_Stop
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
/*
CREATE TABLE TestUniqueNulls
(RowID int IDENTITY NOT NULL,
NoDupName varchar(20) NULL)
*/
?
create trigger trgIU_TestUniqueNulls_Stop
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
ASKER
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_Stop
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
/*
CREATE TABLE TestUniqueNulls
(RowID int IDENTITY NOT NULL,
NoDupName varchar(20) NULL)
*/
/*
create trigger trgIU_TestUniqueNulls_Stop
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
ASKER
OK got it here is the solutions combined
/*
CREATE TABLE TestUniqueNulls
(RowID int IDENTITY NOT NULL,
NoDupName varchar(20) NULL)
*/
/*
create trigger trgIU_TestUniqueNulls_Stop
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_Stop
--select * from TestUniqueNulls
/*
CREATE TABLE TestUniqueNulls
(RowID int IDENTITY NOT NULL,
NoDupName varchar(20) NULL)
*/
/*
create trigger trgIU_TestUniqueNulls_Stop
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_Stop
--select * from TestUniqueNulls
you need help or u find the solution please close the quetion if so.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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