SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 using Relation ??? help me
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chulz90
Starting Member

Indonesia
26 Posts

Posted - 11/29/2012 :  02:58:19  Show Profile  Reply with Quote
NOTE:This item reserved by DO_Number below
DO_Number Item Qty
DO_001 BR_001 5
DO_002 BR_001 3
DO_003 BR_001 4

The case is, when i generate new DO_Number below
DO_Number Item Qty
DO_004 BR_001 8

when DO_004 booked item BR_001
is there any query to show the NOTE above, to announce me
there are another DO_Number that has booked item BR_001 based by DO_004

please help me

bandi
Flowing Fount of Yak Knowledge

India
2208 Posts

Posted - 11/29/2012 :  03:53:04  Show Profile  Reply with Quote
Write a Insert Trigger

CREATE TRIGGER Tr_Insert
ON tab 
FOR INSERT 
AS
BEGIN
	SELECT 'These items are reserved by DO_number below '	SELECT t.* 
	FROM tab t 
	JOIN inserted i ON t.Item = i.Item AND t.DO_Number != i.DO_Number
END


--
Chandu

Edited by - bandi on 11/29/2012 03:53:29
Go to Top of Page

chulz90
Starting Member

Indonesia
26 Posts

Posted - 11/29/2012 :  04:13:11  Show Profile  Reply with Quote
quote:
Originally posted by bandi

Write a Insert Trigger

CREATE TRIGGER Tr_Insert
ON tab 
FOR INSERT 
AS
BEGIN
	SELECT 'These items are reserved by DO_number below '	SELECT t.* 
	FROM tab t 
	JOIN inserted i ON t.Item = i.Item AND t.DO_Number != i.DO_Number
END


--
Chandu



thank you so much bud.. thank's to reply my post you're so kind
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2208 Posts

Posted - 11/29/2012 :  04:17:51  Show Profile  Reply with Quote
quote:
Originally posted by chulz90


thank you so much bud.. thank's to reply my post you're so kind



Welcome

--
Chandu
Go to Top of Page

chulz90
Starting Member

Indonesia
26 Posts

Posted - 11/29/2012 :  04:31:22  Show Profile  Reply with Quote
quote:
Originally posted by bandi

quote:
Originally posted by chulz90


thank you so much bud.. thank's to reply my post you're so kind



Welcome

--
Chandu



oh yaaa, i want to know the function (!) in line 8 ? ? ?
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2208 Posts

Posted - 11/29/2012 :  04:38:05  Show Profile  Reply with Quote
JOIN inserted i ON t.Item = i.Item AND t.DO_Number != i.DO_Number

inserted is the referencing table which holds recently inserted data. So we are joining this table with actual table (based on matching item value) and then excluding recently inserted DO_number from the output



--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000