December 3, 2009 at 4:24 am
Hi All
I have created a table as shown below
Create table #temp (ID int,Levelid int, value int)
Insert into #temp (1,1,100)
Insert into #temp (1,2,200)
Insert into #temp (1,3,300)
Insert into #temp (1,4,400)
Insert into #temp (2,1,100)
Insert into #temp (2,2,200)
Insert into #temp (2,3,300)
Insert into #temp (2,4,400)
I need to write a trigger so that when a user tries to insert a value that is less value than the maximum value on the table, it should not allow the user to enter, say for example, if a user tries to enter
Insert into #temp (2,5,400) or Insert into #temp (1,5,400)
The trigger should throw an exception and say the value must be higher
Thanks in advance for your help
December 3, 2009 at 4:47 am
Can you show what you tried to do? It seems to me that you need to get the current maximum value that exists in the table with select max query and compare it with the value that you get with select min query on inserted. After you compare the numbers you can use the raiserror statement. By the way I think that you should do it in an instead of trigger and not in trigger. The reason for that is that trigger runs after the insert statement and in case that the record wasn’t a good one, you need to rollback the transaction (or use a delete statement, but I would prefer rollback). If on the other hand you use an instead of trigger, you check the record and you run the insert statement only if it is a valid record.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 3, 2009 at 5:27 am
Thanks Adi
i have done the below, but its not working
ALTER TRIGGER [dbo].[TrCheckMax] ON [dbo].[DefinePayThruServiceAmountBasedLevels] After Insert,Update
AS
BEGIN
Declare @NewLevel Tinyint
Declare @NewAmount int
Declare @NewID int
Declare @MaxAmount int
select * into #TempTrigger from inserted
Select @NewAmount=value,@NewLevel=Levelid,@NewID=Service from #Temp
Select @NewAmount,@NewLevel,@NewID
Select @MaxAmount=max(AmountSpent) from #TempTrigger1 (nolock) where Service=@NewID
Select @MaxAmount
if((Select max(amountSpent) from #TempTrigger1 (nolock) where service=1) > @NewAmount)
Begin
RAISERROR ('Please Check Amount and LevelID on tbale,Amount Spent must not be Less than previous level',10, 1)
RollBack
End
Thanks :_)
December 3, 2009 at 6:48 am
In your code, you created one temporary table, but you are trying to get data from another 2 tables:
--Here you create table that is called #TempTrigger
select * into #TempTrigger from inserted
--Here you are trying to get data from a table that is called #Temp
Select @NewAmount=value,@NewLevel=Levelid,@NewID=Service from #Temp
--Here you are working with table that is called #TempTrigger1
Select @MaxAmount=max(AmountSpent) from #TempTrigger1 (nolock) where Service=@NewID
Notice that I don’t think that you should use any temporary table. I think that something along this lines should do the job:
IF EXISTS (select * from Inserted as iINNER JOIN TrCheckMax as t
on t.ID = I.ID and t.LevelID = i.LevelID
where t.value > i.value)
RAISERROR ('Please Check Amount and LevelID on tbale,Amount Spent must not be Less than previous level',10, 1)
Be sure to test it.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply