_trigger - 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

SQL Server 2000 Forums

SQL Server Development (2000)

_trigger

Author

Topic

mike009
Starting Member

15 Posts

Posted - 2006-03-25 : 23:29:03

hi all i have a proble creating atrigger
here ar my tables

1- product
productid (primary)
...

2-orderline
orderID(foreign to order table)
productid(foreign to product table)
..

3-order
orderid(primary)
customerid(foreign to customer table)
salesmanid(foreign to employee table)

4-customer
customerid (primary key)
...

now i have to create a trigger that when a new product inserted
every customer will receive one with a price of 0

how can i create this one coz as you see there are 2 tables between product and customer
thx

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-03-26 : 01:29:19

In which table price column is there??

And on inserting into product table does the values in the orderline table are also updated???

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-26 : 02:18:34

This sounds like a homework question

quote:
"now i have to create a trigger that when a new product inserted
every customer will receive one with a price of 0"

Basically you need to create the insert trigger on the product table and insert the product into another table.

"every customer will receive one with a price of 0"
Which table is this ? is it customer table ? Is there a productid column in the customer table ?

"there are 2 tables between product and customer"
What do you mean by this ?

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

mike009
Starting Member

15 Posts

Posted - 2006-03-26 : 06:52:01

hi all
thx you guys for replaying .
yes khtan this is homework , but i don't want you to make it for me i just don't understand how to do it , i don't ask for the code i just ask for any tips that helps me to find the solution, i think this is allowed? if not, i'm sorry.

about the price
fix price is in product table
and there is also price that is depends of the amount and this one is in orderline table

quote:

"every customer will receive one with a price of 0"
Which table is this ? is it customer table ? Is there a productid column in the customer table ?

"there are 2 tables between product and customer"
What do you mean by this ?

that's is the hard part for me , product is connected to orderline and orderline connected to order and order connected to customer table
so ther is no direct connection between product table and customer table

but i think this will be in the orderline table so every time a new product inserted every customer will will get one with a price of 0 in the orderline table (coz the price in the product table will not be zero) but the problem is how to link all of together.

i don't know where to start, if i create new product and then link it to orderline then i need orderid then order should exsist , or i have to create order for each customer before ????????
ok i will add the tables :


CREATE TABLE [product] (
	[ProductID] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Model] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Description] [nvarchar] (160) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Size1] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Size2] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Price] [int] NOT NULL ,
	CONSTRAINT [pk_product] PRIMARY KEY  CLUSTERED 
	(
		[ProductID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO


CREATE TABLE [orderline] (
	[OrderID] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ProductID] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Quantity] [int] NULL ,
	[Unit_price] [int] NULL ,
	CONSTRAINT [PK_ordlineId] PRIMARY KEY  CLUSTERED 
	(
		[OrderID],
		[ProductID]
	)  ON [PRIMARY] ,
	CONSTRAINT [fk_order] FOREIGN KEY 
	(
		[OrderID]
	) REFERENCES [le_Order] (
		[Order_id]
	),
	CONSTRAINT [fk_productId] FOREIGN KEY 
	(
		[ProductID]
	) REFERENCES [product] (
		[ProductID]
	)
) ON [PRIMARY]
GO


CREATE TABLE [le_Order] (
	[Order_id] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[OrderDate] [smalldatetime] NULL ,
	[Customer_id] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Salesman_id] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	CONSTRAINT [pk_order] PRIMARY KEY  CLUSTERED 
	(
		[Order_id]
	)  ON [PRIMARY] ,
	CONSTRAINT [fk_customer] FOREIGN KEY 
	(
		[Customer_id]
	) REFERENCES [le_Customer] (
		[Customer_id]
	),
	CONSTRAINT [fk_salesman] FOREIGN KEY 
	(
		[Salesman_id]
	) REFERENCES [employeeProf] (
		[EmployeeProfID]
	)
) ON [PRIMARY]
GO


CREATE TABLE [le_Customer] (
	[Customer_id] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CustomerName] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Adress] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	CONSTRAINT [pk_customer] PRIMARY KEY  CLUSTERED 
	(
		[Customer_id]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-03-26 : 07:10:15

for linking every table you need to use series of joins..

For eg if you want to link customer table to your product table then following will be sql statement..

Select Product.ProductID,le_Customer.Customer_Id From Product
Inner Join OrderLine On Orderline.ProductID = Product.ProductID
Inner Join Le_Order On orderLine.Order_ID = Le_Order_Id
Inner Join Le_Customer on le_Customer.Customer_Id = le_Order.Customer_Id

you can look out for different type of joins on Book Online..
Hope this makes sense..

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources