473,691 Members | 2,751 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 #1
10 11523
Pilgrim333
127 New Member
Are you sure any records are updated?

Pilgrim.
Oct 30 '08 #2
gyanendar
90 New Member
Are you sure any records are updated?

Pilgrim.
In the table records are getting updated but trigger is still taking Old value.
Nov 3 '08 #3
Pilgrim333
127 New Member
Ok,

So now you have values in the table where col3 has the value 'Y' Try to update col3 now, and see if the trigger_table is updated.

Pilgrim.
Nov 3 '08 #4
gyanendar
90 New Member
Ok,

So now you have values in the table where col3 has the value 'Y' Try to update col3 now, and see if the trigger_table is updated.

Pilgrim.

No ,its not updating trigger_table values.
If before updation table contains 'Y' than its working .
Nov 3 '08 #5
amitpatel66
2,367 Recognized Expert Top Contributor
try using this code:

Expand|Select|Wrap|Line Numbers
  1.  
  2. CREATE OR REPLACE TRIGGER TEST_TRIGGER
  3. AFTER UPDATE OF col3 on table_gyan
  4. DECLARE
  5. PRAGMA AUTONOMOUS_TRANSACTION;
  6. v_count NUMBER:=0;
  7. v_temp_count NUMBER:=0;
  8. BEGIN
  9. SELECT Count(1) into v_count from table_gyan where col3=:new.col3;
  10. If v_count>0 THEN
  11. SELECT COUNT(1) INTO v_temp_count from TRIGGER_TABLE;
  12. if v_temp_count>0 THEN
  13. UPDATE TRIGGER_TABLE SET ACTION='START';
  14. COMMIT;
  15. else
  16. insert into TRIGGER_TABLE(ACTION) values('START');
  17. commit;
  18. end if;
  19. END IF;
  20. END;
  21.  
Nov 4 '08 #6
Pilgrim333
127 New Member
As this is a statement level trigger and firest once every statement, the individual values are not available in the trigger.

But the post of amitpatel66 makes us think, make it a row level trigger and use the code amitpatel66 posted and see if the trigger_table table gets updated.

Pilgrim.
Nov 4 '08 #7
amitpatel66
2,367 Recognized Expert Top Contributor
Yes this is for row level trigger.

@OP, are you looking at Row level trigger updates or statment level? Please confirm?
Nov 4 '08 #8
gyanendar
90 New Member
Yes this is for row level trigger.

@OP, are you looking at Row level trigger updates or statment level? Please confirm?
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
Nov 7 '08 #9
Pilgrim333
127 New Member
So, the same code works for row level statement, but doesn't work for the statement level trigger?

Pilgrim.
Nov 7 '08 #10

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

Similar topics

2
16703
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
5409
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
4745
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
14336
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
3939
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
6518
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
2251
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
2695
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
19470
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
8531
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
8951
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
8794
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...
0
7623
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...
0
4322
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4550
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2965
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
2227
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1952
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.