Need help with Trigger! Need to update database using ASP

[1661]

Hi,

We have a database in SQL Server. We are using ASP pages to update some of the data. We have a trigger set that if the rent entered is greater than 1000 it will fire. How do I make it work in ASP?? Please reply ASAP!

Here is the code for my trigger:
CREATE TRIGGER TooMuchRent
on team07_PropertyForRent_tbl
For Insert
AS
DECLARE @propertyNo char(5)
Set @propertyNo = (Select propertyNo from INSERTED)
If (Select rent FROM INSERTED) >1000
BEGIN
PRINT 'Assign expereinced staff member to property number '+ @propertyNo + '.'
End

 

[MeanGreen]

The trigger will still fire no matter what. However, I think you are asking how does the ASP code get the PRINT statement back. To be honest, I am not sure. Who is supposed to get the message? Have you considered emailing the notification? What if you have 3 properties in the same insert and they are all over 1000? You trigger will only handle the last one. You might want to revisit your trigger to make it handle multiple instances and not just ONE TRANSACTION at a time. For example:

CREATE TRIGGER TooMuchRent
on team07_PropertyForRent_tbl
For Insert
AS
BEGIN
SELECT 'Assign experienced staff member to property number '+ inserted.propertyNo + '.' from inserted where rent > 1000
End

Hope this helps.

 

[1661]

Thanks for the help first of all. But we actually need to return the message on the screen. Secondly, the trigger is not working because when we have tried adding values greater than 100 it has been added to the database. PLEASE HELP!!! thanks a lot!

 

[MeanGreen]

Again, if you look at what I rewrote for your trigger, it will work only for rent over 1000. Sorry I can't help you much on the return value to ASP. I have worked a little with ASP, but would be like the blind leading the blind in this case. You might try posting to the ASP forum. I will be glad to help you with the SQL, but ASP is not my strong point.

Hope this helps.

 

[rt63]

You can think of using temp tables to store the messages, display them and then drop them.

e.g.

In your ASP page just before you give the insert/update statement, fire a query which creates a temp (double hash) table

create table ##temp_table (comments varchar(200))

Then in your trigger instead of the select statement, you can write as

insert into ##temp_table
select 'Assign experienced staff member to property number '+ inserted.propertyNo + '.' from inserted where rent > 1000

Then in your ASP page after the insert/update you can view if this table contains any records, then you can display them and then drop this ##temp_table.



RT

 

[schwarzs]

I am not sure what your main goal is but I think it is that you want to return a message to the user at the moment the the user submits the value from the ASP page. In this case, what our programmer do, is run that in a stored procedure. The reason being is that you can do the inserting of the value into the database if it is below the 1000 mark or send back a message to the client computer is it is over. We write it as

CREATE PROCEDURE [dbo].[AddRecordtoRent]

@Rent currency
AS
declare @mess char(1)

if @rent > 1000
begin
set @mess = 'Y'
end
else
begin
set @mess = 'N'
insert into rent
(VALUES)
select *
from rent
end
select @mess 'This value gets sent back to the ASP page
as a record set then you can evaluate the value and decied or the course of action.



Hope this helps!!!