SQL Server Programming - Sybase trigger

Author

Sybase trigger

Alan

2006-01-31, 7:09 pm

I making a migration from Sybase SQL Anywhere to SQL Server 2005.
Is it possible to convert the following trigger in SQL Server without to use
a Cursor.

create trigger DBA.ZOC_1001_01_update before update order 1 on DBA.ZOC_1001_01
referencing old as old_name new as new_name
for each row begin
if new_name.modified = 99 then
set new_name.modified=0
else
set new_name.modified=11
end if
end

Razvan Socol

2006-01-31, 7:09 pm

Hi, Alan

I'm not familiar with Sybase triggers, but I guess you want something
like this:

CREATE TRIGGER YourTriggerName ON YourTable
FOR UPDATE
AS
SET NOCOUNT ON
UPDATE YourTable
SET modified=CASE WHEN i.modified=99 THEN 0 ELSE 11 END
FROM YourTable t INNER JOIN inserted i ON t.ThePK=i.ThePK

Replace "ThePK" with the primary key in your table (assuming that there
is a single-column primary key).

If you have other triggers that need to be fired and you don't have
"recursive triggers" on (by default it's off), you should use an
INSTEAD OF UPDATE trigger, like this:

CREATE TRIGGER YourTriggerName ON YourTable
INSTEAD OF UPDATE
AS
SET NOCOUNT ON
UPDATE YourTable
SET modified=CASE WHEN i.modified=99 THEN 0 ELSE 11 END,
SomeOtherColumn1=i.SomeOtherColumn1,
SomeOtherColumn2=i.SomeOtherColumn2
[...]
FROM YourTable t INNER JOIN inserted i ON t.ThePK=i.ThePK

Replace "SomeOtherColumn1", "SomeOtherColumn2", etc with the name of
each column in your table, except the primary key and the "modified"
column.

On second thought, it may be better to use an INSTEAD OF trigger, in
any case.

Razvan

Mark Williams

2006-01-31, 7:09 pm

SQL Server uses INSTEAD OF triggers rather than "before" triggers, and
INSTEAD OF triggers always fire first, before any AFTER triggers. Without
seeing the DDL for the table ZOC_1001_01, I'm just guessing that "modified"
is the name of a column.

CREATE TRIGGER ZOC_1001_01_update
ON ZOC_1001_01
INSTEAD OF UPDATE
AS
BEGIN
UPDATE ZOC_1001_01 SET modified = CASE WHEN i.modified = 99 THEN 0
ELSE 11 END
/*SET statements for other columns in the table would go here */
FROM ZOC_1001_01 z INNER JOIN inserted i
ON z.primarykeycolumn = i.primarykeycolumn
END

--
"Alan" wrote:

> I making a migration from Sybase SQL Anywhere to SQL Server 2005.
> Is it possible to convert the following trigger in SQL Server without to use
> a Cursor.
>
> create trigger DBA.ZOC_1001_01_update before update order 1 on DBA.ZOC_1001_01
> referencing old as old_name new as new_name
> for each row begin
> if new_name.modified = 99 then
> set new_name.modified=0
> else
> set new_name.modified=11
> end if
> end

Sponsored Links