CODEWITHSUNDEEP
sql-serverstored-proceduresviewtriggers
edwardinchains
edwardinchains

Reputation: 119

Execute Stored Procedure using Trigger on VIEW when multiple columns are updated with specific values

I have a VIEW (in SQL SERVER) containing the following columns:

itemID[vachar(50)]|itemStatus [vachar(20)]|itemCode[vachar(20)]|itemTime[varchar(5)]

The itemID column contains id values that do not change. The remaining 3 rows however get updated periodically. I understand it is more difficult create a trigger on a VIEW.

An example of the table containing data would be:

 |itemID|imtemStatus|itemCode|itemTime|
 |------|-----------|--------|--------|
 |  1   |  OK       |  30    |  00:10 |
 |------|-----------|--------|--------|
 |  2   |  OK       |  40    |  02:30 |
 |------|-----------|--------|--------|
 |  3   |  STOPPED  |  30    |  00:01 |
 |------|-----------|--------|--------|

When itemStatus = STOPPED & itemCode = 30

I would like to execute a stored procedure (sp_Alert) passing the itemID as a parameter

Any help would be greatly appreciated

Upvotes: 0

Views: 751

Answers (1)

Steef
Steef

Reputation: 333

Since a trigger is at least "not easy", I'd like to propose an ugly but functional way out. You can create a stored procedure that checks ItemCode and ItemStatus. If they match your criteria you can start the sp_Alert from this procedure.

create procedure check_status as 
if (select 1 
      from vw_itemstatus 
     where itemStatus = 'STOPPED' 
       and itemCode = 30) is not null
begin
declare @item_id int 
    set @item_id = (select itemID 
                      from vw_itemstatus 
                     where itemStatus = 'STOPPED' 
                       and itemCode = 30)
    exec sp_Alert @item_id
end

Depending on how critical this functionality is and how many resources you can use for it, you can schedule this procedure via the SQL Server Agent. If you run this with a short interval, it will work "similar" to what you had in mind.

Upvotes: 1

Related Questions