Insert/Update trigger question--how to know if insert or update

Is there an easy way to tell in a single trigger for both insert/update
whether it was an insert or update.  I know how to do this in transact sql
(for each statement) , but the for each row thing is messing me up.  in
transact sql I can just count the number of deleted rows (if 0 then its an
insert) but in the sql anywhere syntax its kinda strange--

Thanks
Dave Stienessen
davids@xata.com



0
Dave
11/4/1998 8:11:57 PM
sybase.sqlanywhere.general 32637 articles. 4 followers. Follow

2 Replies
1279 Views

Similar Articles

[PageSpeed] 46

Couldn't you check if there's an 'old' row? If there is it's an update,
otherwise it's an insert.

David.

Dave Stienessen wrote:

> Is there an easy way to tell in a single trigger for both insert/update
> whether it was an insert or update.  I know how to do this in transact sql
> (for each statement) , but the for each row thing is messing me up.  in
> transact sql I can just count the number of deleted rows (if 0 then its an
> insert) but in the sql anywhere syntax its kinda strange--
>
> Thanks
> Dave Stienessen
> davids@xata.com

0
David
11/5/1998 5:55:25 AM
Hi Dave,

There's sort of a way, but you decide if it's easy. I've included a couple
of scripts I've used with ISQL so you can cut-and-paste and try for
yourself. Debug() is a procedure that calls the Win32 API OutputDebugString
so I can see what's going on.

HTH

Dave.

First script:-

/**************************************************************
*
* Trigger: RXB_TEST
*
* Purpose: To see if it's possible to determine at runtime
*    whether the before row level trigger has fired
*    as a result of delete, insert or update and set
*    the @action variable to 1, 2 or 3 accordingly.
*
* Notes:  It is apparently not possible to distinguish
*    between delete and update. For some bizarre
*    reason new_row is available and the same as
*    old_row for delete.
*
*    Not only that, but the database engine dies
*    sometime after the final Debug call when a
*    delete trigger is fired.
*
***************************************************************/
if exists (select 1 from "SYS"."SYSTRIGGER" where "TRIGGER_NAME" =
'RXB_TEST') then
 drop trigger "RXB_TEST"
end if;

create trigger "RXB_TEST" before delete,insert,update on "TEST"
referencing new as new_row old as old_row for each row
begin
 declare @column_not_found exception for sqlstate '52003';

 declare @action integer;
 declare @newval integer;
 declare @oldval integer;

 set @action = 0;

 begin
  set @oldval = old_row.h;
  set @action = @action + 1;

  exception
   when @column_not_found then
    set @action = @action;
   when others then
    resignal;
 end;

 begin
  set @newval = new_row.h;
  set @action = @action + 2;

  exception
   when @column_not_found then
    set @action = @action;
   when others then
    resignal;
 end;

 call Debug('RXB_TEST: action = ' || string(@action) || ', old value = ' ||
string(@oldval) || ', new value = ' || string(@newval));

end;

Second script:-

/**************************************************************
*
* Trigger: RXA_TEST
*
* Purpose: To see if it's possible to determine at runtime
*    whether the after row level trigger has fired
*    as a result of delete, insert or update and set
*    the @action variable to 1, 2 or 3 accordingly.
*
* Notes:  It is apparently not possible to distinguish
*    between delete and update. For some bizarre
*    reason new_row is available and the same as
*    old_row for delete.
*
***************************************************************/
if exists (select 1 from "SYS"."SYSTRIGGER" where "TRIGGER_NAME" =
'RXA_TEST') then
 drop trigger "RXA_TEST"
end if;

create trigger "RXA_TEST" after delete,insert,update on "TEST"
referencing new as new_row old as old_row for each row
begin

 declare @column_not_found exception for sqlstate '52003';

 declare @action integer;
 declare @newval integer;
 declare @oldval integer;

 set @action = 0;

 begin
  set @oldval = old_row.h;
  set @action = @action + 1;

  exception
   when @column_not_found then
    set @action = @action;
   when others then
    resignal;
 end;

 begin
  set @newval = new_row.h;
  set @action = @action + 2;

  exception
   when @column_not_found then
    set @action = @action;
   when others then
    resignal;
 end;

 call Debug('RXA_TEST: action = ' || string(@action) || ', old value = ' ||
string(@oldval) || ', new value = ' || string(@newval));

end;



Dave Stienessen wrote in message ...
>Is there an easy way to tell in a single trigger for both insert/update
>whether it was an insert or update.  I know how to do this in transact sql
>(for each statement) , but the for each row thing is messing me up.  in
>transact sql I can just count the number of deleted rows (if 0 then its an
>insert) but in the sql anywhere syntax its kinda strange--
>
>Thanks
>Dave Stienessen
>davids@xata.com
>
>
>


0
Dave
11/5/1998 10:44:23 AM
Reply:

Similar Artilces:

Trigger to Insert Row on Update and Insert
Hi, I'm sure this question has been asked before, but I couldn't find an answer. When a row in TableA is inserted or updated, how can I insert that row into TableB? In other words TableB is like an audit tracking table. I can't figure out how to get the uniqueID of the row in TableA. Many thanks. Chris Why do you need the get the uniqueId of the row?  Regardless, it should already be set and available in the Trigger. However, if the two tables have the exact same fields the Trigger code would simply be Create Trigger trgSomeName On TableA For Insert, Updat...

How can i do a multiple insert or multiple updates or inserts and updates to the same table..
Hi...  I have data that i am getting through a dbf file. and i am dumping that data to a sql server... and then taking the data from the sql server after scrubing it i put it into the production database.. right my stored procedure handles a single plan only... but now there may be two or more plans together in the same sql server database which i need to scrub and then update that particular plan already exists or inserts if they dont...   this is my sproc...  ALTER PROCEDURE [dbo].[usp_Import_Plan] @ClientId int, @UserId int = NULL, @HistoryId int, @ShowS...

Updates Updates Updates
I was a big fan of Firefox.. Now, I don't even like to use it. Seems like every time I start it up I have to update some addin OR the browser itself. And unfortunately the update nag messages are always presented when I start a browsing session. I find this annoying because, typically, when I open a web browser all I want to do is go to a web page. Not update the browser. I can agree that staying current with the latest version could give the best, fastest, safest web experience. But that point becomes mute when I start off every other browsing session with a new update. And mos...

updates, updates, updates
Name: hans jensen Email: hansjensen44_athotmaildotcom Product: Firefox Summary: updates, updates, updates Comments: why is it everytime i go online firefox has new updates, 3 -4 times every day, its never ending..........whats all the problems??? please email me and let me know, ty,.........Hans Jensen Browser Details: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.2) Gecko/20070219 Firefox/2.0.0.2 ...

updates, updates, updates, NO MORE!
Name: Andreas Email: unbekanntatjokerdotms Product: Firefox Summary: updates, updates, updates, NO MORE! Comments: Hi ! if you want to develop a fine webbrowser, maybe you nee a new management. To release a new buggy version every two weeks is not very professional. Test your software before the release and let the user download a stable browser. this update-orgy since 2.0.0 used me to use Opera instead of FF. We haved use FF in our Company, but we can't deploy a new version every week to +200 Windows-clients, so in our company we've decided to return to IE, ...

Updates, updates and more updates
Name: pirhan Email: pirhan_chanathotmaildotcom Product: Firefox Summary: Updates, updates and more updates Comments: Hello, Normally I use Safari, but my iBook is out of commission for a while. So, I am forced to use a PC to which I would much rather use Firefox than Internet Exploder. However, as much I enjoy not using a Microsoft product, and use a product that works decently, I am severely annoyed about the constant updates I am put through. In the last two days, I had to wait for Firefox to install updates before I could browse and now, I'm alerted that there'...

updates, updates, updates
Holy Crap, Batman! You'd think I was running Windows XP and it's patch Tuesday. So far this week - new kernel (2.6.27.10), 2 new browsers (Opera 9.63 and Firefox 3.05) and a new plugin (Flash 10.0.156.3). I'm about ready to try one od the BSD's! vince wrote: > Holy Crap, Batman! You'd think I was running Windows XP and it's patch > Tuesday. > > So far this week - new kernel (2.6.27.10), 2 new browsers (Opera 9.63 > and Firefox 3.05) and a new plugin (Flash 10.0.156.3). I'm about ready > to try one od the BSD's! I take ...

VS2005 - using detailsview to update, insert and delete rows from SQL 2005 database. Delete and insert work but update does not
  Using VS 2005 DetailsView to insert, delete, and update rows in SQL 2005 database.  insert and delete work but update does not.  I recieve no errors and the detailsView comes back unchanged (as well as table row is unchanged).   I am trying to use as little code behind as possible. However I do have ItemUpdating routines that seem to work (i.e Checking table for new login duplicates and encrypting passwords).  The following is the source code generated by VS2005:<%@ Page Language="VB" AutoEventWireup="false" CodeFile="frmDbRegionMgr...

Should "update code lines" and "insert code lines" be same page.aspx or be different (update.aspx and insert.aspx)??
hi friends Should "update code lines" and "insert code lines" be same page.aspx or be different (update.aspx and insert.aspx)? if i use same page.aspx for this, code lines be 1000 lines in page.aspx.vb but one page connect to masterpage... if i use different *.aspx for this, code line be 500 lines in per *.aspx.vb  for per *.aspx... but two pages connect to masterpage.. So i have almost 60 pages (*.aspx).. if i select different *.aspx for update and insert, my pages will be 120 pages (*.aspx) which one should i select ? which one runs high performance ? co...

Can I roll back certain query(insert/update) execution in one page if query (insert/update) in other page execution fails in asp.net
Can I roll back certain query(insert/update) execution in one page if  query (insert/update) in other page  execution fails in asp.net.( I am using sqlserver 2000 as back end)  scenario In a webpage1, I have insert query  into master table and Page2 I have insert query to store data in sub table.  I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance Hi, sre...

Insert/Update question
Hi, I am building an e-commerce site for a college project and in my database design I am using autonumbers/Identity for primary keys for things like order numbers. I am using SQL Server 2005 and have the keys as int's and with identitys that start and 1 and increment by one.  I know that when I do an insert the table will automatically put a number in the id field. My question is if I have to do an insert on multiple tables, lets say and order table and order history table.  How do I get the generated id number into the foriegn key on the other table?  Is there someway...

insert trigger on an update
I heard that there is a problem, in Version 11, where insert triggers get fired on updates. Is this still True in version 12? Thanks, Mike I don't recall any such problem in version 11 (or any version, for that matter). What exactly did you hear? -bret mike wrote: > I heard that there is a problem, in Version 11, where insert triggers get > fired on updates. > > Is this still True in version 12? > > Thanks, > Mike <mike> wrote in message news:E8FCF10D8DEAD2EC0062A3CD85256B9F.0062A43585256B9F@webforums... > I heard that ther...

disable delete trigger when inside insert/update trigger
I think I've figured this out from previous posts, but would like expert critique on the approach. For a specific table, I need to "disable" my delete trigger when I'm inside my insert/update trigger. (The insert/update trigger deletes rows from the table and then inserts "calculated" rows. The delete trigger does a similar operation, but I only want this behavior for a "direct" delete, not when the delete happens inside the insert/update trigger.) 1. I'm going to create a global temporary table (ConnectionVariable - probably with name a...

Update GridView after DetailsView update/insert/delete
I'm missing something here... I am using a DetailsView to handle all of the update/deletes/inserts for the Selected gridview record. Displaying the information works fine (after catching on to the filter changes in beta 2). I'm simply trying to figure out how to refresh the data in the gridview after update/insert/delete events in the detailsview. I don't think I'm interested in a programatic update of just the selected row -- a refresh of all the data is fine with me. I've tried a .Select on the objectdatasource and  a .Databind  -- doesn't seem to get the job done (I...

Trigger on insert, update, delete
I have a trigger on insert and update, that simply reads "inserted.code" (the primary key from the table) and stores it into a second table "modified_rows_list" I do the same in the trigger for delete, except that it uses "deleted.code" to get the primary key. Is there a way to have a single trigger on insert, update and delete, that reads the primary key either from "inserted" or from "deleted" as needed? Thanks in advance AS How about insert modified_rows_list select code from inserted union select code from deleted (I&...

INSERT vs. UPDATE question
I have a Sql2000 database located on a remote server that contains a table named tblLinkStore which has a columns named ID_L and L_Rank, both of type Int.  I also have a dynamically generated GridView which also contains columns named ID_L and L_Rank.   When I generate the GridView it recalculates the L_Rank value.  I want to write that newly generated L_Rank value into the remote Sql table WHERE tblLinkStore.ID_L = GridView's ID_L . Do I need to look at doing an UPDATE or an INSERT?  I thought INSERT was only to add new records.&nb...

Scripting insert and update triggers
I looked on the CodeXchange and didn't see what I was looking for. Or maybe I'm looking for the wrong keyword or phrase. I want to create template code to generate an insert or update trigger to populate the audit columns I created with Mike's code (thanks again). Has anyone done this? Or is there a better way? Thanks, Karen karen.case@apcwireless.com wrote... > I looked on the CodeXchange and didn't see what I was looking for. Or > maybe I'm looking for the wrong keyword or phrase. > > I want to create template code to generate an insert o...

After insert, update and delete triggers
I have 2 tables : invoices headers and invoices details. I want to write a trigger to maintain the total amount on the invoices header (it's simply the sum of the amounts of the corresponding details). So I thought on something like this : CREATE TRIGGER "invoices_total_amount" AFTER DELETE, INSERT, UPDATE ORDER 1 ON "DBA"."invoices_details" REFERENCING OLD AS old_rec NEW AS new_rec FOR EACH ROW BEGIN DECLARE ldb_total double; SELECT sum ("invoices_details"."amount") INTO ldb_total ...

Trigger fired by Insert or Update?
How can I know if the trigger fired by Insert or Update? ...

After Insert, Update Delete Trigger
Using ASA 6.0.3.3114 still..... I'm interested in creating a simple Insert, Update, Delete row level trigger to record the fact that a record has changed. To do this I ran two tests. Test 1 - Referencing New as XXX in an After Insert, Update, Delete will return a value XXX.ColumnName after a delete. Test 2 - Referencing Old as XXX in an After Insert, Update, Delete will not return a value XXX.ColumnName after an insert. So here's my question, can I rely on this Test 1 behavior not changing in future versions? Is there any reason I shouldn't use Test 1? ...

Insert / Update of trigger events
This is a multi-part message in MIME format. ------=_NextPart_000_00AC_01C5C8F4.271266E0 Content-Type: multipart/alternative; boundary="----=_NextPart_001_00AD_01C5C8F4.271266E0" ------=_NextPart_001_00AD_01C5C8F4.271266E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi Using 8.0.2.4476 I have problems with defining triggers with multiple = trigger events: ---------------------- create trigger TempShiftBooked after insert, update on TempShift referencing new as NewShift for each row when (NewShift.S...

Trigger fired by Insert or Update?
How can I know if the trigger fired by Insert or Update? 1. From the trigger definition -- it part of the trigger header... 2. From Sybase Central, when you see list of triggers, change display to show details. It is "Events" column. Igor "Luis Ortega" <luis@mksoft.com> wrote in message news:wdVIzL47AHA.262@forums.sybase.com... > How can I know if the trigger fired by Insert or Update? I was going to suggest coding a MESSAGE 'this trig. has fired' but You have coded a combined instert,update trigger here haven't you? ...

Triggers and Testing for Update/Insert
With triggers that are defined as "BEFORE[/AFTER] INSERT, UPDATE", is there a way to tell if the trigger was fired due to an update vs an insert? It would be nice to have something that could be used in an IF statement to execute a block of code only for an insert and not for an update etc. I cannot get the "IF UPDATE (column-name) THEN" syntax to work. Referencing oldrec.column-name during an insert causes the trigger to fail. This is not a critical question since I can split the trigger and have one for update, and one for insert, but it would be nice to know h...

Trigger on UPDATE,INSERT,DELETE
Hi! Please prompt how can determine which event occur in trigger on UPDATE,INSERT,DELETE ADA 7.0 -- Best regards, Andrew Leyba As of 8.0.1 you can use these statements within your triggers (there is no way to do it in 7.x) Triggers can be defined to fire for INSERT, UPDATE, DELETE or any combination of these statements. Previously it was difficult/awkward to determine within the trigger what type of statement caused it to fire. Statements like the following can now be used to determine this: if inserting then .. . . elseif deleting then .. . . elseif updating('...