May 29, 2012 at 9:48 pm
weisen678 (5/28/2012)
SS, now, I have tried again, this time I get a red leggy under my column name 'inventoryQty', when my mouth over, I get this message,Invalid column name 'InventoryQty'.
...
Your own code had "NEW.InventoryQty", I didn't make it up.
You should script out your PurchaseOrder table and post that here. We really can't help you much further without that.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 30, 2012 at 4:57 am
Thanks really, SS, here is my schema of the purchaseOrder table
PurchaseOrder table
POrdID (PK)
ProdID (FK)
OrdQty
it is quite simple table.
May 30, 2012 at 5:12 am
Dear SS,
I have found the script of the table for insert
INSERT INTO [ProjectTesting].[dbo].[PurchaseOrder]
([POID]
,[ProdID]
,[POQty])
VALUES
(<POID, int,>
,<ProdID, int,>
,<POQty, int,>)
GO
May 30, 2012 at 5:25 am
the ProdID has a value, the quantity has a fixed value, but how I can assign a variable value to POID?
May 30, 2012 at 5:42 am
SS, do you know is there any if statement to compare the value? instead of where statement, can we use if after update the value become smaller than the value given?
May 30, 2012 at 7:49 am
As stated above you need to provide some consumable ddl (create table statements), sample data (insert statements) and desired output. There is little anybody can do to help until everyone has the same tables and such to work with.
Take a look at the first link in my signature.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 30, 2012 at 4:37 pm
Thanks Sean ans SS Barry!
here is my
1) create tabel for PurchaseOrder system script;
2) insert into system script;
3) sample data from the actual PurchaseOrder table
1) --create table
USE [ProjectTesting]
GO
/****** Object: Table [dbo].[PurchaseOrder] Script Date: 05/31/2012 08:16:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PurchaseOrder](
[POID] [int] NOT NULL,
[ProdID] [int] NOT NULL,
[POQty] [int] NULL,
CONSTRAINT [PK_PurchaseOrder] PRIMARY KEY CLUSTERED
(
[POID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PurchaseOrder] WITH CHECK ADD CONSTRAINT [FK_PurchaseOrder_Product] FOREIGN KEY([ProdID])
REFERENCES [dbo].[Product] ([ProdID])
GO
ALTER TABLE [dbo].[PurchaseOrder] CHECK CONSTRAINT [FK_PurchaseOrder_Product]
GO
2) -- Insert Row;
INSERT INTO [ProjectTesting].[dbo].[PurchaseOrder]
([POID]
,[ProdID]
,[POQty])
VALUES
(<POID, int,>
,<ProdID, int,>
,<POQty, int,>)
GO
3) -- sample data
select '33', '1100', '600' union all
select '34', '1200', '600' union all
May 31, 2012 at 7:39 am
I am guessing you didn't read that article as most of this is pretty useless.
here is my
1) create tabel for PurchaseOrder system script;
The create table portion is ok but the foreign keys are referencing tables we don't have.
2) insert into system script;
This is actually just what an insert statement would look like for this table. Doesn't actually provide anything.
3) sample data from the actual PurchaseOrder table
As posted this doesn't work. Try making this into a runnable insert statement like this.
INSERT PurchaseOrder
select 33, 1100, 600 union all
select 34, 1200, 600
I am not trying to bust your chops here but we are all volunteers and spending time to clean up untested code takes away time that would otherwise be spent working on your problem instead of cleaning up insert statements before we can begin.
After all that you have reference to an Inventory table in your trigger. How about ddl and sample data for that one.
The point of posting this stuff is to simulate your real environment so we can write code against structures and data that will be the same as your real version.
Last but not least your original post said you wanted to insert data into another table.
I have a project to compare value from a table, if the value is smaller then the static value say 200, then my trigger will fire to insert a row in another table
Your trigger for updates on the PurchaseOrder table and your insert is inserting data into the same table. Help us understand your requirements and post some solid ddl along with some sample data. This is really a pretty simple thing when we have the whole picture.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 31, 2012 at 4:35 pm
Sean,
I am quite new to SQL.
but at the begining, I have said, this is my study project, so, the tables are my real enviornment. there are three tables,
product table contribute ProdID foreign key to PurchaseOrder table
Inventory tabel has InventQty column and ProdID
what I want to do is to detect that once the inventQty drop below set value, then the trigger start to insert a row into PurchaseOrder table.
Therefore, the trigger deal with two tables, inventory table and PurchaseOrder table.
here are my two tables;
inventory table
inventID (PK)
ProdID (FK)
inventoryQty
PurchaseOrder table
POrdID (PK)
ProdID (FK)
OrdQty
May 31, 2012 at 6:09 pm
weisen678 (5/31/2012)
Sean,I am quite new to SQL.
but at the begining, I have said, this is my study project, so, the tables are my real enviornment. there are three tables,
product table contribute ProdID foreign key to PurchaseOrder table
Inventory tabel has InventQty column and ProdID
what I want to do is to detect that once the inventQty drop below set value, then the trigger start to insert a row into PurchaseOrder table.
Therefore, the trigger deal with two tables, inventory table and PurchaseOrder table.
here are my two tables;
inventory table
inventID (PK)
ProdID (FK)
inventoryQty
PurchaseOrder table
POrdID (PK)
ProdID (FK)
OrdQty
I know you are new to SQL and I am willing and able to help. However you need to provide your information in a consumable format. You need to read the article I suggested. It will seriously take you five minutes. If you had read that article and posted your information in a consumable format this would be done already. Instead you are now 2-3 days into this and no closer to an answer.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 1, 2012 at 2:28 am
Thanks very much :-), SS!
I really do not know what else I should do. I did read the article flolow your advice and posted the system script from my tables with some data, in the table purchaseOrder there are only two rows data.
this is only my study project. nothing really sophisticate or significant, all I tried to find out is if there is any SQL statement to compare one table's one single column value with a static value and then insert into another table a row.
I have found a lot of articles give sample code for if updating one table, then do something in another table, but I haven's find any anwser to compare the set value, if condition true, then insert to asnother.
this is the reason I tried to ask for help, to see if someone knows how to compare the fixed value with one table then trigger insert into another table.
June 1, 2012 at 8:07 am
Well you didn't mention that this was an attempt to learn a concept until quite late. Regardless the same need for ddl and such still exists. Just because it is not an actual business case the sql still needs actual tables and such to work with. Because you are new and are trying to understand I mocked something up for you.
Take note of how I put together the create table and sample data.
--first we need a product table
create table Product
(
ProdID int identity,
ProductName varchar(20),
ReorderThreshold int,
ReorderQty int
)
--Now we need to fill it with some products
insert Product
select 'Test Product 1', 5, 50 union all
select 'Test Product 2', 10, 20 union all
select 'Test Product 3', 5, 75
--Need to track our inventory
create table Inventory
(
ProdID int,
QOH int
)
--how much do we have on hand currently?
insert Inventory
select 1, 8 union all
select 2, 15 union all
select 3, 6
--we need a PO table so we can reorder stuff
create table PurchaseOrder
(
PO_ID int identity,
ProdID int,
OrdQty int
)
go
--Just so we can see what is in the tables now.
select * from Inventory
select * from Product
select * from PurchaseOrder
go
--Now if I understand what you wante you wanted to see how to evaluate the data
--in another table during the update. For this example creating the table on the Inventory
--table is where this would happen. In other words when an order is made we would update
--the inventory table. when this update occurs, IF the new quantity is less than or equal
--to our reorder threshold we need to automatically generate a PO.
create trigger InventoryLow on Inventory for update as begin
set nocount on
insert PurchaseOrder
select i.ProdID, p.ReorderQty
from inserted i
join Product p on i.ProdID = p.ProdID
where i.QOH <= p.ReorderThreshold
end
go
--this update will reduce the QOH to less than our threshold so would expect
--a PO to automatically generated.
update inventory set QOH = 3 where ProdID = 1
--Let's look and see if it worked
select * from PurchaseOrder
Hopefully this makes sense and is somewhat like the scenario you are trying to figure out.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 1, 2012 at 3:31 pm
Thanks SS!!!
I have also modified a bit and I will try it out later today.
Thanks kindly!:-)
June 4, 2012 at 1:44 am
Dear SS, sorry for the delay to get back to you late. I just finished one exam on diffirent subject this morning, I had to concentrate on that.
now, I come back to this project, I tested on the code you created, then I get this error message
Msg 213, Level 16, State 1, Procedure InventoryLow, Line 4
Column name or number of supplied values does not match table definition.
here is your code
create trigger InventoryLow on Inventory for update as begin
set nocount on
insert PurchaseOrder
select i.ProdID, p.ReorderQty
from inserted i
join Product p on i.ProdID = p.ProdID
where i.InventQty <= p.ReorderThreshold
end
what I think is I need to set a value for the PO_ID in the PurchaseOrder table, as the table has three columns, above code only provided two columns, but I do not know where should I set a value for the PO_ID, SS, do you know where should I set this value for the PO_ID? I think a fixed value will be OK.
Thanks in Advance and Greatly Appreciated for your time and all the help. I learn a lot from conversation with your guys!
June 4, 2012 at 8:16 am
weisen678 (6/4/2012)
now, I come back to this project, I tested on the code you created, then I get this error message
Msg 213, Level 16, State 1, Procedure InventoryLow, Line 4
Column name or number of supplied values does not match table definition.
what I think is I need to set a value for the PO_ID in the PurchaseOrder table, as the table has three columns, above code only provided two columns, but I do not know where should I set a value for the PO_ID, SS, do you know where should I set this value for the PO_ID? I think a fixed value will be OK.
If you change the insert to include all 3 columns it will fail because PO_ID is an identity. It works perfectly with the ddl I provided. If you have a different table structure then of course it won't work without some modification.
It seems in the ddl you provided that POID is not an identity. Because mostly because the ddl provided was incomplete I just created my own, it is probably not the same as yours.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply