September 22, 2003 at 7:30 pm
Please help me. I create an update trigger to prevent users from updating my data if the date of that record <'8/31/03'. However, the exception is they still can update data if the record they want to update is not existed in another table. Please advice me how to do it. Below is my trigger. The problem is I could not update data eventhrough the record_date is greater >'8/31/03'.
create trg_POUpdate as
(select i.* from inserted i, deleted d, purchase_order po
where i.[date]= d.[date]
and i.chrontel_PO# = d.chrontel_PO#
and i.rev = d.rev
and d.[date]= po.[date]
and d.[chrontel_po#] = po.[chrontel_po#]
and d.rev = po.rev
and (po.chrontel_po# in (select chrontel_po# from final_po where date <'8/31/03'))
and po.rev in (select rev from final_powhere date '8/31/03'))
begin
RAISERROR ('You can not change or add past record because month end has been closed.', 16,1)
Rollback Tran
END
September 22, 2003 at 8:44 pm
I'm a little confised by the code on what's being done here, how about this
create trigger tbltrg on table
for insert,update,delete
as
declare @one datetime,@two datetime
set @one = (select date from inserted)
set @two = (select date from deleted)
if (@one <= '2003-08-31') OR (@two <= '2003-08-31')
begin
raiserror ('No way jose',16,1)
rollback tran
end
else
commit tran
Ray Higdon MCSE, MCDBA, CCNA
------------
Ray Higdon MCSE, MCDBA, CCNA
September 23, 2003 at 6:02 am
It's very hard to read your trigger pseudo code, particularly as it does not match your pseudo DDL. Based on your narrative, I suppose you want something like this:
CREATE TRIGGER t_u_Purchase_Order ON Purchase_Order
FOR UPDATE AS
IF EXISTS
(SELECT *
FROM Final_Purchase_Order f JOIN deleted d ON f.PO# = d.PO# AND f.Rev = d.Rev
WHERE d.Record_Date < '20030831')
BEGIN
RAISERROR ('One or more rows fails the test', 16,1)
ROLLBACK TRAN
END
Updates to more than one row will be prevented if any row fails the test, but that's the structure you chose. It's odd to allow something on the last day of the month but not on previous days in the month; are you sure you don't want "< '20030901'"?.
A business rule like this, where I'm sure the cutoff date will change, probably should not be in a trigger. I suggest putting this logic in a stored procedure which wouldn't need to use ROLLBACK, could accept the cutoff date as a parameter (or derive it if you've got an algorithm), and which could selectively allow updates.
--Jonathan
--Jonathan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply