473,836 Members | 2,035 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to access new value Statement Level Trigger

90 New Member
Hi All,
I want to access new value after upadate in statement level trigger.
But in my query I am getting old values.
Here is the Trigger Code:

CREATE OR REPLACE TRIGGER TEST_TRIGGER
AFTER UPDATE OF col3 on table_gyan
DECLARE
PRAGMA AUTONOMOUS_TRAN SACTION;
v_count NUMBER:=0;
v_temp_count NUMBER:=0;
BEGIN
SELECT Count(1) into v_count from table_gyan where col3='Y';
If v_count>0 THEN
SELECT COUNT(1) INTO v_temp_count from TRIGGER_TABLE;
if v_temp_count>0 THEN
UPDATE TRIGGER_TABLE SET ACTION='START';
COMMIT;
else
insert into TRIGGER_TABLE(A CTION) values('START') ;
commit;
end if;
END IF;
END;

.


Now when ever I am executing this query
update table_gyan set col3='Y'
where col1='3'
and col2='100'
Its not doing anything with TRIGGER_TABLE.


Please help me how to do it.

Regards,
Gyanendar
Oct 29 '08
10 11536
amitpatel66
2,367 Recognized Expert Top Contributor
Yes,
Finally I made it row level trigger and every thing worked fine.

But,if 100 rows of table get updated than ,tigger will get fired for all the 100 rows and does the same work.To avoid this problem ,I was thinking for statement level trigger.

Guy's can we have statement level trigger for this work.

Regards,
Gyanendar
No you cannot. If you want it to work for all the records then you should have row level trigger
Nov 7 '08 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

2
16728
by: Allan Hart | last post by:
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,
14
5430
by: Sean C. | last post by:
Helpful folks, Most of my previous experience with DB2 was on s390 mainframe systems and the optimizer on this platform always seemed very predictable and consistent. Since moving to a WinNT/UDB 7.2 environment, the choices the optimizer makes often seem flaky. But this last example really floored me. I was hoping someone could explain why I get worse response time when the optimizer uses two indexes, than when it uses one. Some context:
6
4768
by: Peter Frost | last post by:
Please help I don't know if this is possible but what I would really like to do is to use On Error Goto to capture the code that is being executed when an error occurs. Any help would be much appreciated. Thanks in advance
49
14369
by: Yannick Turgeon | last post by:
Hello, We are in the process of examining our current main application. We have to do some major changes and, in the process, are questionning/validating the use of MS Access as front-end. The application is relatively big: around 200 tables, 200 forms and sub-forms, 150 queries and 150 repports, 5GB of data (SQL Server 2000), 40 users. I'm wondering what are the disadvantages of using Access as front-end? Other that it's not...
7
3950
by: peter.morin | last post by:
Issue: I am inserting an Oracle record containing insert trigger via Access 2002 using the code below. The issue is that the sequence from the acSaveRecord is not reflected after the insert so the insert into the second table section: ' Copy LOB entries now. fails on unique index constraint (understandable because it has the orig rate_id). The strange thing is that it works fine for new records that have beend added and then...
6
6532
by: Hardy Wang | last post by:
Hi all, I have the following codes, but SCOPE_IDENTITY() just returns NULL to me. If I comment out SCOPE_IDENTITY() line and run @@IDENTITY line, it works fine!! Since I have a trigger on the table, I have to use SCOPE_IDENTITY(). Any ideas? SqlConnection conn = new SqlConnection(connectionString); conn.Open(); //Create the dataadapter
4
2266
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...
17
2705
by: Rico | last post by:
Hello, I am in the midst of converting an Access back end to SQL Server Express. The front end program (converted to Access 2003) uses DAO throughout. In Access, when I use recordset.AddNew I can retrieve the autonum value for the new record. This doesn't occur with SQL Server, which of course causes an error (or at least in this code it does since there's an unhandled NULL value). Is there any way to retrieve this value when I add a...
2
19507
hyperpau
by: hyperpau | last post by:
Before anything else, I am not a very technical expert when it comes to VBA coding. I learned most of what I know by the excellent Access/VBA forum from bytes.com (formerly thescripts.com). Ergo, I will be writing this article intended for those who are in the same level, or maybe lower, of my technical knowledge. I would be using layman's words, or maybe, my own words as how I understand them, hoping, you will understand it the same way that...
0
10548
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...
1
10591
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9374
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
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
5826
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4452
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
4016
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3115
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.