Re: OO and relation "impedance mismatch"

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 _at_trxNum int declare _at_trxMade int set _at_trxMade = 0 if (select count(*) from zuuxtrxnum where spid = _at__at_spid) = 0 begin set _at_trxMade = 1 insert into zuuxtrxnum (spid) values (_at__at_spid) end select _at_trxNum=skey from zuuxtrxnum where spid = @@spid

--

• This is also standard for all triggers, specific
• to each operation and table
  --
  declare _at_errTab char(35) declare _at_errOp char(1) set _at_errTab = 'acrmcustcall' set _at_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 _at_trxNum,13100,_at_errOp,_at_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 _at_trxMade allows nested triggers to work
• properly.
  --
  IF _at_trxMade > 0 BEGIN if (Select count(*) from zuuxtrxerr where skeytrx = _at_trxNum) > 0 begin SET NOCOUNT OFF SELECT errnum,errop,tabname,errpkey,errdsc FROM zuuxtrxerr WHERE skeytrx = _at_trxNum ROLLBACK TRANSACTION end delete from zuuxtrxnum where spid = _at__at_spid END END

-- 
Kenneth Downs
Use first initial plus last name at last name plus literal "fam.net" to
email me