Trigger and procedure

given a propertyid and sale amount the system should change property status to 'sold'
when it is marked sold askingprice should be added to totalsales.

property(property_id,property_name,asking_price,st atus,total_size,address,city,state,agent_id)

agent(agent_id,last_name,first_name,start_date,con tact_no,total_sales,property_id)

re: Trigger and procedure

Sounds like homework. What have you done so far?

re: Trigger and procedure

some how not getting the idea to do it

help me

re: Trigger and procedure

create proc sp_prop_status
(@property_id int,@asking_price float(2))
as
begin
If not exists (select * from property where property_id=@property_id)
begin
select 'not a valid property'
return
end

begin transaction
update property set status = 'sold'
where property_id=@property_id
if @@error <>0 or @@rowcount <> 1
begin
rollback transaction
select 'insert failed'
return
end
commit transaction
end

This is what i have done

re: Trigger and procedure

im not able to implement the 2nd part adding asking_price to total_sales when it is marked sold

re: Trigger and procedure

delerna or administrator plz help me out

re: Trigger and procedure

Looks like you're expecting to update only 1 record with this condition @@rowcount <> 1, otherwise it rolls back.

-- CK

re: Trigger and procedure

yes ur right after updating status i should be able to use trigger to update total_sales

re: Trigger and procedure

I mean record, not column.

If your table have many rows that will be updated @@rowcount is > 1 hence it rolls back.

If this is a homework, you have to figure out the rest.

-- CK