[ipac] Staff-Only Update for item insert/update trigger
Brad Bates
babates at pclc.lib.fl.us
Wed Dec 20 16:35:21 EST 2006
Hello to all,
One of the set of annoyances about Horizon is:
-- Items that "should" be marked "Staff Only" due to a status change,
e.g., Lost or Missing, are not marked "Staff Only".
-- Bib records that have every item attached to them marked as "Staff
Only" are not also marked "Staff Only" and thus show up in search results
even though no items are available.
-- Items that should NOT be marked "Staff Only" due to a status change,
such as those that are checked in after they are lost and somehow marked
Staff Only, do not get changed.
-- Bib records that only had "Staff Only" items attached to them and were
somehow marked as Staff Only are not changed out of that state when one of
the items attached to them are changed out of that state.
Simply put, the software places the onus on the library staff to mark or
unmark items and bibs as Staff Only. And, if your library/libraries are
anything like ours, this does not happen more often than not because
everyone is way too busy to get this done manually all of the time.
Which leads to things like the HIP system listing results for items and bibs
that have no items available (lost or missing or whatever), questions from
borrowers about said items and bibs, etc.
To solve this problem I added the following code to the item insert/update
trigger (item_iu_trig trigger) in the Horizon database.
/* PCLC code */
/*
Items that are changed TO status of 'c' (claimed returned), 'dis'
(disappeared)
'l' (lost), and 'm' (item missing) have their "staff_only" value set to 1
(true).
*/
if exists (select * from inserted
where item_status = 'c'
or item_status = 'dis'
or item_status = 'l'
or item_status = 'm')
begin
update item
set staff_only = 1
from item, inserted
where item.item# = inserted.item#
/*
Bibs that only have "staff_only" items attached have their "staff_only"
value
set to 1 (true).
*/
if NOT exists (select * from item it, inserted i
where it.bib# = i.bib#
and it.staff_only = 0)
begin
update bib_control
set staff_only = 1
from bib_control, inserted
where bib_control.bib# = inserted.bib#
end
end
/*
Items that have their "staff_only" value set to 1 and are not changed TO
status
of 'c' (claimed returned), 'dis' (disappeared) 'l' (lost), and 'm' (item
missing)
have their "staff_only" value set to 0 (false).
*/
if exists (select * from inserted
where staff_only = 1
and item_status != 'c'
and item_status != 'dis'
and item_status != 'l'
and item_status != 'm')
begin
update item
set staff_only = 0
from item, inserted
where item.item# = inserted.item#
/*
Bibs that HAD only "staff_only" items attached have their "staff_only"
value
set to 0 (false).
*/
if exists (select * from item it, inserted i
where it.bib# = i.bib#
and it.staff_only = 0)
begin
update bib_control
set staff_only = 0
from bib_control, inserted
where bib_control.bib# = inserted.bib#
end
end
/* End of PCLC code */
Basically, the code sets the "Staff Only" value for items whenever an item's
status changes to something that makes it useless to borrowers. In our case
the status (stati?) values are claimed returned, disappeared, lost, and
missing. I should probably include missing inventory too, but for now that
is what we use.
The code also checks to see if the item's bib has any other items that are
not in a 'staff only' state and, if it does not, then the bib is set to
staff only too.
And the rest of the code changes things back as needed, when items are
found, returned, or whatever.
You will need to use the item status code values that are appropriate for
your setups to make this work on your system. Other than that this should
work okay for you as is, but it is still an "at your own risk" addition
should you choose to use it.
I hope you find this useful. Perhaps someone at SirsiDynix will put this
simplistic functionality into version 8.x....
Brad Bates
Polk County Library Cooperative
The entire trigger from our database follows....
=====================================================
/*
Copyright 2000-2002 epixtech, inc. All rights reserved.
Copyright 2003-2004 Dynix Corporation. All rights reserved.
use training
drop trigger item_iu_trig
*/
create trigger item_iu_trig
on item for insert, update as
declare @num_rows int,
@colSize int,
@item# int
select @num_rows = @@rowcount
if update (bib#)
/* foreign referential integrity, null NOT allowed */
if @num_rows !=
(select count(*) from bib, inserted
where bib.bib# = inserted.bib#
and bib.tag = '000'
and substring(bib.text,6,1) != 'd')
begin
rollback transaction
raiserror 20001 "bad or deleted bib# in item"
return
end
if update (location)
begin
/* foreign referential integrity, null NOT allowed */
if @num_rows !=
(select count(*) from location, inserted
where location.location = inserted.location)
begin
rollback transaction
raiserror 20001 "bad location in item"
return
end
/* insert into pms_item on all new inserts or a change to item.location
*/
insert pms_item (item#, action)
select item#, 2 from inserted
where delete_flag = 0
end
if update (collection)
begin
/* foreign referential integrity, null NOT allowed */
if @num_rows !=
(select count(*) from collection, inserted
where collection.collection = inserted.collection)
begin
rollback transaction
raiserror 20001 "bad collection in item"
return
end
/* DeCounts.exe now updates the counts. This is needed for the first
item so the collection cannot be deleted. The 'if exists' is needed
to prevent locking the table if there is no update needed. */
if exists (select * from collection c, inserted i
where i.collection = c.collection and c.n_items = 0)
update collection
set n_items = 1
from inserted i, collection c
where i.collection = c.collection and c.n_items = 0
end
if update (itype)
begin
/* foreign referential integrity, null NOT allowed */
if @num_rows != (select count(*) from itype, inserted
where itype.itype = inserted.itype)
begin
rollback transaction
raiserror 20001 "bad itype in item"
return
end
/* DeCounts.exe now updates the counts. This is needed for the first
item so the itype cannot be deleted. The 'if exists' is needed
to prevent locking the table if there is no update needed. */
if exists (select * from itype it, inserted i
where i.itype = it.itype and it.n_items = 0)
update itype
set n_items = 1
from inserted i, itype it
where i.itype = it.itype and it.n_items = 0
end
if update (item_status)
begin
/* foreign referential integrity, null NOT allowed */
if @num_rows != (select count(*) from item_status, inserted
where item_status.item_status = inserted.item_status)
begin
rollback transaction
raiserror 20001 "bad item_status in item"
return
end
/* DeCounts.exe now updates the counts. This is needed for the first
item so the item_status cannot be deleted. The 'if exists' is needed
to prevent locking the table if there is no update needed. */
if exists (select * from item_status it, inserted i
where i.item_status = it.item_status and it.n_items = 0)
update item_status
set n_items = 1
from inserted i, item_status it
where i.item_status = it.item_status and it.n_items = 0
/* insert circ when changing item_status to 'o' */
if exists (select * from inserted where item_status = 'o')
insert circ (borrower#, item#, proxy_borrower#)
select borrower#, item#, proxy_borrower#
from inserted
where item_status = 'o'
/* delete pick_list_source when changing item_status from 'rb' to
anything else reformulated query to join (JMC) */
if exists (select * from deleted where item_status = 'rb')
delete pick_list_source
from pick_list_source p, deleted d where p.item# = d.item#
/* insert pick_list_source when
changing item_status to 'rb' */
if exists (select * from inserted where item_status = 'rb')
insert pick_list_source (rbr_location, pick_location, item#)
select saved_location, location, item#
from inserted
where item_status = 'rb'
/* PCLC code */
/*
Items that are changed TO status of 'c' (claimed returned), 'dis'
(disappeared)
'l' (lost), and 'm' (item missing) have their "staff_only" value set to 1
(true).
*/
if exists (select * from inserted
where item_status = 'c'
or item_status = 'dis'
or item_status = 'l'
or item_status = 'm')
begin
update item
set staff_only = 1
from item, inserted
where item.item# = inserted.item#
/*
Bibs that only have "staff_only" items attached have their "staff_only"
value
set to 1 (true).
*/
if NOT exists (select * from item it, inserted i
where it.bib# = i.bib#
and it.staff_only = 0)
begin
update bib_control
set staff_only = 1
from bib_control, inserted
where bib_control.bib# = inserted.bib#
end
end
/*
Items that have their "staff_only" value set to 1 and are not changed TO
status
of 'c' (claimed returned), 'dis' (disappeared) 'l' (lost), and 'm' (item
missing)
have their "staff_only" value set to 0 (false).
*/
if exists (select * from inserted
where staff_only = 1
and item_status != 'c'
and item_status != 'dis'
and item_status != 'l'
and item_status != 'm')
begin
update item
set staff_only = 0
from item, inserted
where item.item# = inserted.item#
/*
Bibs that HAD only "staff_only" items attached have their "staff_only"
value
set to 0 (false).
*/
if exists (select * from item it, inserted i
where it.bib# = i.bib#
and it.staff_only = 0)
begin
update bib_control
set staff_only = 0
from bib_control, inserted
where bib_control.bib# = inserted.bib#
end
end
/* End of PCLC code */
update item
set last_status_update_date = datediff (dd, '1 Jan 1970', getdate())
from item, inserted
where item.item# = inserted.item#
end
if update(delete_flag)
begin
/* The next two if statements ensure that no rows are deleted for
items that are either checked out or part of a current request.
If you modify these statements, be sure to also modify the code
in DbThanatos.mod which performs these same checks programatically.
*/
if exists(select * from circ c, inserted i, deleted d
where c.item#=i.item#
and i.delete_flag=1
and d.delete_flag=0)
begin
rollback transaction
raiserror 20001 "Can't delete item that is checked out."
return
end
if exists(select * from request r, inserted i, deleted d
where (r.item#=i.item# or r.fill_item#=i.item#)
and i.delete_flag=1
and d.delete_flag=0)
begin
rollback transaction
raiserror 20001 "Can't delete item that has been requested."
return
end
delete advanced_booking
from advanced_booking, inserted
where inserted.delete_flag = 1
and advanced_booking.booked_item# = inserted.item#
delete circ_history
from circ_history, inserted
where inserted.delete_flag = 1 and circ_history.item# = inserted.item#
delete reliance_exception
from reliance_exception, inserted
where inserted.delete_flag = 1
and reliance_exception.item# = inserted.item#
/* insert pms_item: deletes. Here, we must update an existing row or
insert
a new row into pms_item with action=3. We'll do both and one will
succeed. */
update pms_item set action = 3
from pms_item, inserted
where inserted.delete_flag = 1
and pms_item.item# = inserted.item#
insert pms_item (item#, action)
/* may get thrown out by ignore_dup_key--that's the idea! */
select item#, 3 from inserted
where delete_flag = 1
end
if update(due_date) or update(due_time)
begin
if exists(select b.item# from burb b, inserted
where (b.block = "od" or b.block = "odr")
and b.item# = inserted.item# and b.borrower# =
inserted.borrower#)
begin
rollback transaction
raiserror 20001 "Can't change due date if over due block exists, try
renewing the item."
return
end
end
if update(call) or update(call_reconst)
begin
declare @processed varchar(255),
@reconst varchar(255),
@reconstructed varchar(255)
select @colSize = col_length('item','call_reconstructed')
select @item# = min(item#) from inserted
while (@item# is not null)
begin
select @processed = call, @reconst = call_reconst from inserted
where item# = @item#
exec reconstruct @processed, @reconst, @reconstructed output
update item set call_reconstructed =
substring(@reconstructed,1, at colSize)
where item# = @item#
select @item# = min(item#) from inserted
where item# > @item#
end
end
if update(copy) or update(copy_reconst)
begin
declare @processeda varchar(255),
@reconsta varchar(255),
@reconstructeda varchar(255)
select @colSize = col_length('item','copy_reconstructed')
select @item# = min(item#) from inserted
while (@item# is not null)
begin
select @processeda = copy, @reconsta = copy_reconst from inserted
where item# = @item#
exec reconstruct @processeda, @reconsta, @reconstructeda output
update item set copy_reconstructed = substring(@reconstructeda, 1,
@colSize)
where item# = @item#
select @item# = min(item#) from inserted
where item# > @item#
end
end
return
More information about the ipac
mailing list