June 17, 2009 at 6:43 am
Dear All,
I have two tables:
Table1:
CREATE TABLE [dbo].[uitgifteregel](
[ouitgiftenr] [int] NULL,
[oartikelnr] [int] NULL,
[oaantal] [int] NULL
) ON [PRIMARY]
Table2:
CREATE TABLE [dbo].[voorraad](
[oartikelnr] [int] NULL,
[oaantal] [int] NULL
) ON [PRIMARY]
How can i write a trigger/stored procedure:
before inserting a new record into the table one(uitgifteregel), a check occurs, whether the column 'oaantal' in the second table(voorraad) is equal to 0. If it is equal to null then it most give a exception.
Thanks
June 17, 2009 at 7:07 am
i think something like this would work:
CREATE TRIGGER TR_TEXT ON [uitgifteregel]
FOR INSERT
AS
BEGIN
IF EXISTS(SELECT [oaantal]
FROM [voorraad]
INNER JOIN INSERTED
ON [voorraad].[oartikelnr]=INSERTED.[oartikelnr]
WHERE [oaantal] 0)
BEGIN
raiserror ('Invalid Insert; s non zero value for [oartikelnr] exists for one or more of the items isnerted.' ,16,1)
rollback tran
END --if condition
END --proc
Lowell
June 17, 2009 at 7:09 am
I guess it depends on what exactly you want it to do.
For example, does it reject the insert and raise an error, or does it accept the insert but also raise an error, or does it log something instead of raising an error?
Also, I'm not entirely clear on what exactly you're requesting. Do you need general help on how to write triggers in the first place? Are you comfortable with writing triggers, but just can't figure out this particular one? Have you tried writing the trigger and it's not doing what you want? Please clarify, so we know where to start.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 17, 2009 at 7:18 am
Dear GSquared,
Thank you for response. Im a beginner in this and wrote some basic triggers bud can't figure out how to write this one.
It has to reject the insert and give a exception.
Hope that it's clear now.
June 17, 2009 at 7:25 am
In your case I would use a CHECK constraint in combination with a scalar function.
I just wrote a sample in another thread:
http://www.sqlservercentral.com/Forums/FindPost736516.aspx
I don't like triggers very much and try to keep them away if possible 😉
Flo
June 17, 2009 at 7:26 am
Lowell's solution looks almost right. It needs to have a couple of typos fixed, and it checks for a non-zero value instead of null. Your original message mentions checking for a value of zero, and says to raise an exception if it's null, so it's not completely clear.
Here's another option:
create trigger MyTrigger on dbo.uitgifteregel
instead of insert
as
set nocount on;
if exists
(select *
from inserted
left outer join dbo.voorraad
on voorradd.oartikelnr = inserted.oartikelnr
where voorraad.oaantal is null)
begin
raiserror('Failed insert. Null value in dbo.voorraad.', 16, 1)
return
end
insert into dbo.uitgifteregel (ouitgiftenr, oartikelnr, oaantal)
select ouitgiftenr, oartikelnr, oaantal
from inserted;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 17, 2009 at 7:29 am
I don't like triggers very much and try to keep them away if possible
I agree with Florian ...but sometimes they are very nice objects to control (delete, insert or update) actions!
June 17, 2009 at 7:40 am
Dugi (6/17/2009)
[bI agree with Florian ...but sometimes they are very nice objects to control (delete, insert or update) actions!
Hi Dugi
A mouse disagrees with a cat? Brave mouse! 😀
Joking aside...
You are absolutely correct. I do use triggers and there are some really good reasons for - just as you said. Problem is they look very nice and cool at beginning but it's very simple to run into problems with.
Flo
June 17, 2009 at 7:43 am
Hi GSquared,
Sorry if this is a dumb question, bud im a little bit confused.
I did'nt mean null, bud the number 0. Is it the same?
There most occur a exception if the voorraad.oaantal <= 0
June 17, 2009 at 7:48 am
Null and 0 aren't the same in databases. Null means an unknown value, a field that hasn't been provided with a value yet.
In that case, change the "is null" to "<= 0", and you should be okay. Or use Lowell's version, but change the check on that. Either should get you what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 22, 2009 at 11:25 pm
My requirement is similar to it.
But i need to compare two column from two different tables.
I should be able to insert into table1 only when col1 >= col1 of the other table.
Please help me
June 26, 2009 at 11:17 am
sreekirt (6/22/2009)
My requirement is similar to it.But i need to compare two column from two different tables.
I should be able to insert into table1 only when col1 >= col1 of the other table.
Please help me
That check should be done as part of the INSERT. Would really need to see the DDL for the tables involved, some sample data, and expected results based on the sample data.
Please read the first article I reference in my signature block below regarding asking for assistance on how to accomplish this. Also, I'd suggest starting your own thread for the request.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply