CODEWITHSUNDEEP
sqlsql-server
MFox
MFox

Reputation: 43

use insert value as trigger

is it possible to use the insert value in the where clause in SQL?

I would want to use it in this kinda way:

create trigger t_wage_not_higher_than_30000
on transaction
as
if exists
(
select * 
from transaction
where ***inserted value*** >= 30000 and
description = employeewage
)
begin
raiserror('you cannot insert a wage higher than 30000')
rollback transaction
end

Upvotes: 0

Views: 81

Answers (2)

SMor
SMor

Reputation: 2862

For this trigger, you should only refer to the newly inserted rows. Therefore, you need to use the special trigger table in your statement. E.g., 

if exists (select * from inserted 
    where <some column you did not name in 
    your code> >= 30000 and description = 'employeewage') 
begin 
   raiserror ... (or throw);
   rollback tran;
   return;
end;

And yes - a constraint is generally a better approach.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269593

If you want to check the range of values, the best way is to use a check constraint:

alter table transactions add constraint chk_transactions_value
    check (value < 30000);

There is no reason to write a trigger for checking data values.

Upvotes: 2

Related Questions