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

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-11-21 : 07:53:02

erol writes "Dear people,

I have created a trigger which suppose to check wheather the customer is active or not,it works but i dont get the raiserror message. where did i go wrong?
below is the code, please may i have your comments.
thank you in advance
regards
CREATE Trigger CheckOrdersCustomers7
ON tblCustomers
FOR INSERT,UPDATE
AS
BEGIN TRANSACTION
DECLARE @CustomerID int
DECLARE @OrderID int
declare @status bit
select @CustomerID=i.CustomerID
from inserted i
select @OrderID=OrderID
from tblOrders C
where C.OrderID=@OrderID
IF(@status is not null)AND (@status<1)
BEGIN
RAISERROR('The order cannot be processed as the customer is inactive.',16,1)
rollback transaction
end
else
begin
commit transaction
end"

surendrakalekar
Posting Yak Master

120 Posts

Posted - 2005-11-21 : 08:28:56

quote:
Originally posted by AskSQLTeam

erol
IF(@status is not null)AND (@status<1)
BEGIN
RAISERROR('The order cannot be processed as the customer is inactive.',16,1)
rollback transaction
end

What is @Status?
or you want to try something like @@ROWCOUNT.
You declare @status but not used.

Surendra

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-21 : 08:42:11

select @OrderID=OrderID
from tblOrders C
where C.OrderID=@OrderID

Will never do anything because @OrderID is not set.

You might want
if exists (select * from inserted where status <> 1)
begin
RAISERROR('The order cannot be processed as the customer is inactive.',16,1)
rollback transaction
end

But difficult to say from the info given.
Are you sure you want the trigger on tblCustomers?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources