Hello, Ray !

> I can show
>> some examples where trigger of 1-3 lines in Firebird has to be 20-50
>> lines of stupid, slow and ugly code in Oracle and MSSQL - and this
> cases
>> are pretty common.
>>

> Could you pls give a couple of examples? I use MSSQL at work and
> just started FB at home, and would like to avoid applying the MSSQL
> mindset towards FB with regards to triggers. Thanks.

I do not read ib-support.

MSSQL has following problems as compared to Firebird:

1. Very sparse and user-unfriendly syntax to iterate cursors.
It is very easy to declare global cursor (and this behavior depends on
external settings !) and get intermittent crashes.
Example:
--#FIREBIRD
for select balanceacc_id from get_balanceacc_children(:id,:level_count)
into :change_id do
begin
...
end
--#MSSQL
declare acc_cursor cursor local for
select balanceacc_id from get_balanceacc_children(@id,@level_count)
open acc_cursor
fetch next from acc_cursor into @change_id
while @@fetch_status = 0
begin
...
end
close acc_cursor
deallocate acc_cursor

2. Invocation of user functions and procedures in MSSQL is
extremely slow. Something around 100x slower as compared to
Firebird procedures. SCHEMABINDING option doesn't not work in
most cases. This changes programming mindset significantly.
With MSSQL it is often faster to generate complex dynamic TSQL
and do everything inlined. Examples for this problem would be
very big. I have a lot of them.

3. Recursive behavior of triggers and procedures depends on external
settings (whether nested triggers are allowed) and fail after small
value of nesting anyway (32 - and this is hardcoded). Have to use
temporary tables to avoid recursion. This is really slow. Firebird
allows large nesting levels (at least 128 levels of direct recursion).

4. Sequence generators are standartized in recent SQL standards.
MSSQL doesn't support them. See example of warpness it causes lower.

5. There is no support for row triggers. Statement triggers look and
work ugly because of need to use temp tables.
There is no simple way to modify records in PRE-trigger.
Support for ordering of triggers is really ugly. Snippet from
documentation:

>The first and last AFTER triggers to be executed on a table may be
>specified by using sp_settriggerorder. Only one first and one last
>AFTER trigger for each of the INSERT, UPDATE, and DELETE operations
>may be specified on a table; if there are other AFTER triggers on
>the same table, they are executed randomly.

Here is an example of trigger problems in MSSQL, this triggers do
exactly the same thing:
--#FIREBIRD
create trigger ver_budgorder for budgorder before insert or update as
begin
IF (new.version is null or new.version <= old.version) THEN
new.version = gen_id(version_seq,1);
end
--#MSSQL
ALTER TRIGGER ver_BUDGORDER on BUDGORDER for insert, update as
BEGIN
declare @new_ver bigint, @new_id bigint, @old_ver bigint
declare @rows bigint, @record_id bigint
select @record_id = d.id from inserted d
set @rows=@@ROWCOUNT
IF @rows = 1
BEGIN
select @new_ver = i.version, @new_id = i.id from inserted i
select @old_ver = d.version from deleted d
IF (@new_ver is null) or (@new_ver <= @old_ver)
BEGIN
insert into _BUDGORDER_seq(dummy) values(0)
delete from _BUDGORDER_seq where id=@@IDENTITY
update BUDGORDER set version=@@IDENTITY where id=@record_id
END
END
ELSE IF @rows > 1
BEGIN
DECLARE ins_cursor CURSOR LOCAL FOR
select version, id from inserted
OPEN ins_cursor
FETCH NEXT FROM ins_cursor
INTO @new_ver, @record_id
WHILE @@FETCH_STATUS = 0
BEGIN
set @old_ver = null
select @old_ver = version from deleted where id = @record_id
IF (@new_ver is null) or (@new_ver <= @old_ver)
BEGIN
insert into _BUDGORDER_seq(dummy) values(0)
delete from _BUDGORDER_seq where id=@@IDENTITY
update BUDGORDER set version=@@IDENTITY where id=@record_id
END
FETCH NEXT FROM ins_cursor INTO @new_ver, @record_id
END
CLOSE ins_cursor
DEALLOCATE ins_cursor
END
end
------------------


Nickolay Samofatov