471,071 Members | 1,330 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,071 software developers and data experts.

statement level vs. row level triggers

Hi..

I'd very much appreciate it if someone would tell me how to translate
a statement level trigger written in Oracle to its equivalent (if there is
one)
in MS SQL Server. Ditto for a row level trigger.

If this is an old topic, I apologize. I'm very much a newbie to SQL Server.

Regards,
Allan M. Hart
Jul 20 '05 #1
2 16204
In SQLServer, triggers are invoked once per statement. There are no
"row-level" triggers, as such. However, because you have access within the
trigger to the INSERTED and DELETED virtual tables (which contain the
changed rows) it should be possible to implement any logic from your Oracle
row-level triggers using a SQLServer trigger.

The basic syntax for an AFTER trigger is:

CREATE TRIGGER trg_SomeTable ON SomeTable
FOR UPDATE
AS
...

See Books Online for details.

--
David Portas
------------
Please reply only to the newsgroup
--
Jul 20 '05 #2
Allan Hart wrote:
Hi..

I'd very much appreciate it if someone would tell me how to translate
a statement level trigger written in Oracle to its equivalent (if there is
one)
in MS SQL Server. Ditto for a row level trigger.

If this is an old topic, I apologize. I'm very much a newbie to SQL Server.

Regards,
Allan M. Hart


There is also, in SQL Server no equivalent to a BEFORE trigger, DDL
triggers, system triggers, synonyms, and packages. Trying to move an
application built in Oracle to SQL Server is not an easy task unless the
application is very simplistic in its design. A complete rewrite is
often required. Also keep in mind that SQL Server does not have
multiversioning so reads will block writes, writes will block reads and
transactions are not atomic so if a trigger fires you will need to
specifically roll back its actions. Be sure to invest a lot of time in
learning about the many other differences so that your efforts are
successful.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Hans | last post: by
reply views Thread by Simon Reye | last post: by
8 posts views Thread by nano2k | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.