473,836 Members | 1,393 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 16728
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.wash ington.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
2059
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, the lowest is Info. Id Timestamp Level 1 12:00 Info 1 13:00 Warning
1
7238
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 getting the column name programatically. This is all working fine. If I do a regular insert command in my trigger then everything works fine. However, since I want to retrieve data from the column name which I got programatically from the...
4
8561
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 location (document.location.href) via Javascript or using "_TOP" as the target for a SUBMIT operation, etc., then I want to allow it. The other web sites I want to block if they try to do that. I would do the blocking in the top level document...
4
2267
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 whole column as column = colunm + 1 , then there is an error. This obviously works on databases such as mssql which has row level triggers. Any suggestions so that i do not have to modify the query and still make it work in the postgresql database...
0
2692
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 seems as if I don't. If this is the case are there any plans to make available the changed records? Simon
8
3526
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 date that is supplied to the SP as a parameter. The SP is usually a lengthy process (it takes at least 30 mins). The problem is that SQL server 2000 Dev Edition doesn't allow me to insert new invoices that are "younger", while the SP is executing....
0
1739
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 have an events table, within the events table I have 7 columns, Alias varchar (30) Progress int(1) Val double Stamp timestamp
3
15937
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 there any parameter that controls how long DB2 allows a statement to compile for.
0
2110
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 trigger body is thick, and causes other triggers to fire, but no recursion. The triggers have been used in production for many months w/ no issues..
0
9671
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10549
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10254
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7792
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6979
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5828
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4456
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4019
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3116
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.