473,326 Members | 2,133 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 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 16639
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Hans | last post by:
Hello group, I have a table with the next contents. It lists data about : Who sent what kind of message at what time. For the Level column: The highest level is Critical, the middle is Warning,...
1
by: Rebecca Lovelace | last post by:
I have a trigger on a table. I am trying to dynamically log the changed fields on the table to another table, so I am iterating through the bits in COLUMNS_UPDATED() to find what's changed, and...
4
by: Robert Oschler | last post by:
I have a comparison web page that has several iframes that contain documents from external domain web sites. Some of the web sites are "trusted". If they want to change the top level document...
4
by: rama | last post by:
Hi, I am a bit troubled with the row-level triggers which PostgreSQL uses when using update table cpmmand. For instance, if the primary key column has values 1,2,3,... and i want to update the...
0
by: Simon Reye | last post by:
I've looked through all the documentation and I think I am all out of luck. I was wondering if when declaring a statement-level trigger whether I would have access to all the changed records. It...
8
by: nano2k | last post by:
Hi Shortly, I keep invoices in a table. Occasionally, someone will fire the execution of a stored procedure (SP) that performs several UPDATEs against (potentially) all invoices OLDER than a...
0
by: imran haq | last post by:
Hi All, I have 3 rather Long Questions that are causing alot of trouble: I would appreciate all the help i can get and tried to use A post sent to atli in the past but it did not help... !) I...
3
by: Eric Davidson | last post by:
DB2 9.5 I keep geting the message. SQL0101N The statement is too long or too complex. SQLSTATE=54001 When one of my sql statements takes over 60 seconds to compile the sql statement. Is...
0
by: aj | last post by:
DB2 8.2 FP14 LUW Red Hat AS Interesting one here. I modified a trigger body yesterday, changing a numeric constant used in in INSERT statement. I did not add any SQL to the trigger.. The...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.