trigger is the solution? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

trigger is the solution?

Author

Topic

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2010-01-05 : 16:20:11

What is the best solution to implement if I i don't want the column to be inserted to same value? Once I find the value I want to disable this alert and insert the value manually.

example"
col1
6.3
3.4

insert col1
select 6.3
this is raise an error message that tells me i have this value already in the column. Then once i read the msg. I can manually disable the trigger or whaterver i implemented and insert the value manually.

any ideas? trigger is the best solution?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-05 : 17:29:18

I personally wouldn't use a trigger as I avoid them like a plague. I'd instead do something like this:

IF EXISTS (check for the existing data here)
BEGIN
RAISERROR (custom message here)
INSERT...
END

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2010-01-05 : 19:59:07

declare @receiptBal money, @receiptdate datetime, @receiptID int
set @receiptBal = '8.46'
set @receiptdate = '20091202'

-- select * from receipts where receiptdate is null and receiptBal = @receiptBal

if exists (select 1 from receipts where receiptBal = @receiptBal and receiptdate is null )
begin
update receipts
set receiptdate = @receiptdate
where receiptBal = @receiptBal
and receiptdate is null
print 'date updated'
end
else
begin
insert receipts (receiptBal, StoreID, receiptDate)
select @receiptBal, 8, @receiptdate
print 'inserted'
end

select * from receipts where receiptBal = @receiptBal

Ok I implemented your way. After the receiptdate column is updated I run this procedure again and it inserts a new value. How could I stop that?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-06 : 02:06:26

declare @receiptBal money, @receiptdate datetime, @receiptID int
set @receiptBal = '8.46'
set @receiptdate = '20091202'

-- select * from receipts where receiptdate is null and receiptBal = @receiptBal

if exists (select 1 from receipts where receiptBal = @receiptBal and receiptdate is null )
begin
update receipts
set receiptdate = @receiptdate
where receiptBal = @receiptBal
and receiptdate is null
print 'date updated'
end
if not exists (select 1 from receipts where receiptBal = @receiptBal and receiptdate=@receiptdate)
begin
insert receipts (receiptBal, StoreID, receiptDate)
select @receiptBal, 8, @receiptdate
print 'inserted'
end

select * from receipts where receiptBal = @receiptBal

Madhivanan

Failing to plan is Planning to fail

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2010-01-06 : 03:20:00

Madhi,
Thanks that did the job.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-06 : 03:26:08