An Instead of Insert 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 2005 Forums

Transact-SQL (2005)

An Instead of Insert Trigger

Author

Topic

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-08-13 : 12:43:45

Hi all,
I want to create an INSTEAD OF INSERT trigger for following purpose but I cannot do it.
Please help me.
When a customer want to take an order then the trigger must check the customer's credit, if credit >= cost then insert a row to the order table else if credit <= cost then do not insert.

I have two tables


 CREATE TABLE Customers
(
	CustomerID nchar(10) PRIMARY KEY,
	Credit Int NOT NULL
)
CREATE TABLE Orders
(
	OrderID nchar(10) PRIMARY KEY,
	CustomerID nchar(10),
	Cost int
)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-08-13 : 13:03:06

Could you at least show us what you have so far?

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."

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-13 : 13:08:16

>>but I cannot do it
What is preventing you from creating it?

Does the credit need to be greater than (or equal to) just that one customer order or the sum of all the unpaid orders for that customer?

Be One with the Optimizer
TG

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-08-13 : 13:27:30

quote:
Originally posted by TG

>>but I cannot do it
What is preventing you from creating it?

Does the credit need to be greater than (or equal to) just that one customer order or the sum of all the unpaid orders for that customer?

Be One with the Optimizer
TG

I am a beginner.

the credit need to be greater than just that customer order.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-13 : 13:31:09

Fair enough - so I guess this is for school, huh? Because it doesn't make sense to allow 1000 different orders all for 1 dollar less than their customer credit

So as to Tara's question...Could you at least show us what you have so far?

Be One with the Optimizer
TG

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-08-13 : 13:56:07

Imagine I want to insert a row to the orders table with these values:
OrderID='Order3', CustomerID='Customer1', Cost=120 Dollors

The Cost is greater than Costomer's Credit to this reason the row should not is inserted by trigger.


OrderID    CustomerID Cost
---------- ---------- ---------------------
Order1     Customer1  50.00
Order2     Customer1  35.00 

CustomerID Credit
---------- ---------------------
Customer1  100.00

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-13 : 14:15:09

I think we understand the requirements - I believe what Tara was looking for was a little effort on your part before you got your homework handed to you on a platter. So I'll stand with here and wait your response

But if those two rows each had 99.00 dollars you still don't mind adding new orders?
Also, is it ok to all an UPDATE to change a cost to more than the credit?

Be One with the Optimizer
TG

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-08-13 : 15:34:25

Please just tell me how I must fill the ... at this code.


CREATE TRIGGER tr
ON Orders
INSTEAD OF INSERT
AS
...

And please do not ask me more questions

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-13 : 15:45:35

Wow! That has got to be the lamest effort I've seen.

Now I don't feel so bad just "handing" you this homework - you couldn't possibly misrepresent yourself in a job interview.


set nocount on
use tempdb
go
CREATE TABLE Customers
(
	CustomerID nchar(10) PRIMARY KEY,
	Credit Int NOT NULL
)
go
CREATE TABLE Orders
(
	OrderID nchar(10) PRIMARY KEY,
	CustomerID nchar(10),
	Cost int
)
go
insert Customers values (N'00010', 10)
go
create trigger trg_i_orders on orders instead of insert
as
begin
       insert Orders (OrderID, Customerid, Cost)
       select i.OrderID, i.CustomerID, i.Cost
       from   inserted i
       inner join customers c on c.CustomerID = i.CustomerID
       where  c.credit >= i.cost
end
go

insert Orders (OrderID, CustomerID, Cost) values (N'00012', N'00010', 8)
insert Orders (OrderID, CustomerID, Cost) values (N'00011', N'00010', 12)
insert Orders (OrderID, CustomerID, Cost) values (N'00013', N'00010', 8)
insert Orders (OrderID, CustomerID, Cost) values (N'00014', N'00010', 8)

select * from Orders
go
drop table orders
drop table Customers

OUTPUT:
OrderID    CustomerID Cost
---------- ---------- -----------
00012      00010      8
00013      00010      8
00014      00010      8

Be One with the Optimizer
TG

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-08-13 : 16:04:19

Thank you so much.
May you explain about this source "inserted".
I have not written a trigger yet!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-13 : 16:21:34

You're welcome.
read through this topic in Books Online. take a look at the samples and read about the virtual tables [inserted] and [deleted]
Books Online - Create Trigger:
http://technet.microsoft.com/en-us/library/ms189799(SQL.90).aspx

EDIT:
Then work through this topic and its children - great stuff - it's how I learned.
Books Online - Query Fundamentals:
http://technet.microsoft.com/en-us/library/ms190659(SQL.90).aspx

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources