Troels Arvin wrote:
> On Mon, 04 Oct 2004 18:40:56 -0400, Kenneth Downs wrote:
>
>> But if you use a trigger, you can do tell-me-everything-I-did-wrong.
>
> I don't quite understand this: How can the trigger overcome fail-on-first
> error handling?
The trigger records each error that it finds, but instead of stopping it
keeps going. So the more you have, the more that are recorded. It
actually never raises an error in the strict sense, you might say it always
"returns true".
>
>> The
>> best I have so far come up with is to return a table of error codes and
>> descriptions with each save attempt.
>
> This is exactly the kind of error handling I'm looking for.
>
Here is an example of my current experimental form. My own approach is to
generate these triggers out of a data dictionary, as otherwise it becomes
hopelessly unmanageable. But anyway, for SQL Server, an example would be:
CREATE TRIGGER acrmcustcall_INSERT
ON acrmcustcall
FOR INSERT AS
begin
- THIS OPENING gets a standard batch # for every single
- transaction, which would kill any real high volume
- system. It is here so I can work with it in experimental
- mode and ponder a better way to do it. It does have the
- nifty advantage of working well with nested triggers.
set nocount on
declare @trxNum int
declare @trxMade int
set @trxMade = 0
if (select count(*) from zuuxtrxnum where spid = @@spid) = 0 begin
set @trxMade = 1
insert into zuuxtrxnum (spid) values (@@spid)
end
select @trxNum=skey from zuuxtrxnum where spid = @@spid
--
- This is also standard for all triggers, specific
- to each operation and table
--
declare @errTab char(35)
declare @errOp char(1)
set @errTab = 'acrmcustcall'
set @errOp = 'I'
- This is an RI error trap. Note that it is set-oriented,
- and keeps going afterward, even if every row failed.
- This trigger also assumes the use of my standard surrogate
- key column "skey".
insert into zuuxtrxerr (skeytrx,errnum,errop,tabname,errpkey,errdsc)
select @trxNum,13100,@errOp,@errTab,'NO PKEY',
'Department Code is not valid'
FROM inserted i
WHERE i.custcode NOT IN (
SELECT i.custcode FROM inserted i JOIN acrmcust t
ON i.custcode = t.custcode)
- You can put/generate more RI traps here.
- Next might come a unique trap.
--
- The basic idea here is to return error codes. The
- conditional on @trxMade allows nested triggers to work
- properly.
--
IF @trxMade > 0 BEGIN
if (Select count(*) from zuuxtrxerr where skeytrx = @trxNum) > 0
begin
SET NOCOUNT OFF
SELECT errnum,errop,tabname,errpkey,errdsc
FROM zuuxtrxerr
WHERE skeytrx = @trxNum
ROLLBACK TRANSACTION
end
delete from zuuxtrxnum where spid = @@spid
END
END
--
Kenneth Downs
Use first initial plus last name at last name plus literal "fam.net" to
email me
Received on Tue Oct 05 2004 - 07:45:00 CDT
