Introduction to ADF reusable task flows - consume a bounded task flow that gets synchronized with input parameter changes

Task Flows are the primary mechanism in ADF 11g for organizing the workload in manageable chunks that can be developed in a decoupled fashion in relative isolation, then merged together in web pages that implement the desired functionality. Task Flows are also one of the key mechanisms for reuse - along with but for other [...]

Latest ADF 11g (11.1.1.1.0) Rich Client Demo is available

  Today I came across a brief post on the OTN Forum for JDeveloper and ADF (http://forums.oracle.com/forums/thread.jspa?threadID=923587&tstart=0). The latest demo application (11.1.1.1.0) for ADF 11g Faces RC can be downloaded from http://download.oracle.com/otn/java/jdeveloper/111/extensions/adf-richclient-demo.war. It comes as a WAR file that can be deployed on WebLogic 11g. Alternatively, we can turn the WAR file into a JDeveloper project, to inspect [...]

Join Us For The Launch of Oracle Fusion Middleware 11g


Thanks to our good friends at NASA for the launch picture. This is the space shuttle Discovery blasting off on July 4 2006, click for the details of mission STS-121.

Our launch is at 8:00 AM, on Thursday July 9 at the Hilton Hotel - 488 George Street, Sydney.

We're launching Oracle Fusion Middleware 11g and you're invited, click here to register. We're going be joined by one of our product development Vice Presidents - Amit Jasuja - for the keynote session, and we'll follow that with overview sessions on the main product components of the 11g release - Identity Management, Enterprise 2.0, Application Grid and Service Oriented Architecture. Oh, and there's a lucky draw prize too!

7,350 years of development time have gone into this release so taking half a day out of your schedule seems a small price to pay. 7,350 years! That means that if you started developing today, you'd just be getting ready for your launch event in the year 9,359. By which time we will have all hung up our hydrogen powered personal jetpacks and just teleport oursleves everywhere right?

Once again, register here, and I look forward to seeing you there.

-sean

New Release White Paper through Contractors Network

Contractors Network is pleased to announce the latest free of charge release into our extensive Oracle Apps related White Paper Library of:

“A Structured Approach to SQL Query Design”

Authored By Brendan Furey

About Brendan;

Brendan is an Oracle Applications Consultant with over nineteen years Oracle experience, across a wide range of projects and industries. He has worked with many Oracle modules, including Financials, Supply Chain, and CRM, up to 11.5.10. He is technically oriented, and has expert level knowledge of most of the Oracle development tools, as well as Unix and other tools, and also has good functional knowledge.

Recent projects have included: implementation of multi-org and intercompany invoicing within an existing single-org implementation; development of complex interfaces between Oracle instances using different CRM modules, with PO, AP AR; integration design (using middleware) for a European implementation of Oracle ERP (back-office) along with Siebel (front-office) and other systems.

The Purpose of Brendan’s Paper;

The purpose of this document is to describe a structured, graphical approach to the design of SQL queries that may be a useful way of handling complexity without reverting to procedural design. It focuses on sub query structure and join orders, rather than on other areas such as grouping and aggregation, or design patterns. The author has used it to design complex queries with up to 46 table instances, and the approach is demonstrated using a real (rather simpler) example of a custom report within Oracle’s Order Management and Inventory modules (see REF-2 for Oracle’s table specifications).

If you would like request a copy please email myself on rebecca.bragg@oraclecontractors.com or register via the White Paper library and request your copy today.

Annonce : Oracle Fusion Middleware 11g

Retrouvez le webcast du lancement d'Oracle Fusion Middleware 11g

Plus d'informations :

Annonce : SQL Developer Data Modeler

La première release d'Oracle SQL Developer Data Modeler est enfin disponible en téléchargement sur OTN !

BI, APEX, EBS, My Oracle Support, Internals


BI

Does bursting support translation (huh? ok, it's a BI thing I guess, I've personally never seen bursting and translation in the same sentence). Well, it does, according to this article at A BI Publisher developer's diary blog.

APEX

David Peake points out a good item at Chris Muir's blog on Stress and Load Testing Web Applications using apache jMeter.

EBS

Moving to R12? Already there? Have questions? Don't miss


Note: 824414.1 R12.1: Frequently Asked Questions related to the R12.1
Release(Financials)


And from Chris Warticki's blog we have a link to
FREE R12.1 Advisor Webcast



This week at the Oracle E-Business Suite Technology blog:




Support

Another item from Chris Warticki's blog reminds us that the time is at hand to get ready for My Oracle Support.

Internals

I debated whether this falls more into internals (where Tanel Poder is an expert) or performance (where Tanel Poder is an expert). In either case this links page of his is something you should put in your favorites. Lots of goodies on both those topics to be found.

Contractors Network Blogs to be featured in Oracle Scene Magazine

The UKOUG has chosen articles/ blogs from our Website for the up and coming release of Oracle Scene Magazine.

Well Done to Andy Noble and Rory Dwyer for being selected in the up and coming issue for your Articles.

Rory Dwyer wrote Its Not What You Know Its Who You Know

http://blog.oraclecontractors.com/?p=543

Andy Noble wrote How to quickly identify a missing join

http://blog.oraclecontractors.com/?p=454

Well done guys I look forward to reading your Articles in printed form :)

 

Contributions by Sherron Garnett, Infogram Contributor

Contributions by Sherron Garnett, Infogram Contributor

Upcoming My Oracle Support

In 2009, Oracle customers and partners will migrate onto a single My Oracle Support portal. This will include upgrading MetaLink 3 users to the My Oracle Support platform and migrating Classic MetaLink users to My Oracle Support exclusively. In the past few months, several communications have been delivered to our customers and partners. If you missed the communication sent on June 30th, please read now E-mail Blast

My ODTUG Kaleidoscope 2009

Every year ODTUG - Oracle Development Tool User Group - organizes a conference, called Kaleidoscope. This time it was in Monterey, California.   If you have never been to this conference, you are missing out on something great. A lot of great presentations, great food, lots of time to network and meet a lot of great people. Anyway, [...]

Foundation for Innovation - FMW 11g

  One of the taglines I saw last week for the Oracle Fusion Middleware 11g technology stack was ‘ Foundation for Innovation’. I like that one quite a lot. FMW is a stack of various components, underpinnen by the Oracle Database. The stack of WebLogic Server, SOA Suite, ADF and WebCenter at every level provides a [...]

Oracle Fusion Middleware 11g available for download

  At http://www.oracle.com/technology/software/products/middleware/htdocs/111110_fmw.html you wll find all Oracle Fusion Middleware 11g software. This includes: JDeveloper 11.1.1.1.0 with the 11gR1 release of ADF WebLogic 11g (10.3.1) SOA Suite 11g (including BAM, Rules, Mediator, BPEL, Human Workflow, WebServices Manager, Enterprise Manager integrated for WebLogic Server & SOA Suite) Identity Management WebCenter 11g Complex Event Processing  11g Portal, Forms, Reports and Discoverer 11g - running on WebLogic Server [...]

Is Larry getting a little SaaSy?

The last time we looked at Oracle’s SaaS strategy, Oracle chairman Larry Ellison had largely rejected the idea of software-as-a-service, saying it wasn’t profitable enough. He said this, despite the fact that more than a few of his competitors have enthusiastically embraced the on-demand industry.

So has Ellison had a change of heart?

A year later, it looks that way. Oracle announced today SaaS for ISVs, a new pricing model that allows Independent Software Vendors (ISVs) to pay for its Oracle SaaS platform by the month rather than make an upfront investment.  This also allows ISVs to adjust their SaaS offerings to meet customer demand, according to Oracle.

Analyst China Martens calls this new licensing option part of the “continued gradual easing of Oracle into the SaaS arena,” according to the IDG’s Chris Kanaracus in this article.

Oracle has over 10,000 partners that are active ISVs in its partner program, said Judson Althoff, senior vice president, worldwide alliances and channels, in his video post on the Oracle site.  Althoff says that while Oracle has mostly serviced the “higher end of the SaaS market,” the new model will “allow us to reach a much broader base of ISVs, and better cater to you, the partner who wants to roll out a SaaS offering.”

Althoff also said the monthly cost structure will help the ISV “better manage cash flow” and “only pay for just the actual elements for the Oracle platform that (it) used in the previous month.”

Oracle has also begun promoting SaaS for ISVs on its SaaS Knowledge Zone and a new ISV and SaaS blog by Oracle’s Kevin O’Brien, Senior Director of ISV and SaaS Strategy for Oracle’s Worldwide Alliances and Channels organization.  According to an Oracle data sheet, the new pricing model will apply to the Oracle SaaS Platform from the Oracle Database, Oracle Fusion Middleware, Oracle Enterprise Manager and Oracle VM.

Oracle isn’t the only vendor to have made recent on-demand developments. SAP recently released its latest SaaS strategy, which includes on-demand applications for Business Suite customers that use a multi-tenant architecture rather than an isolated tenant model.

Where do you think Oracle stands in the SaaS market? Will this latest SaaS for ISVs offer help Oracle? Or, as some are saying, despite the new pricing model, is Oracle software just too expensive in the first place?

Launchdate 1st July: JDeveloper 11.1.1.1.0 (Bulldog) - Interesting new features

  As part of the announcement on July 1st around Oracle Fusion Middleware 11g, Oracle released the next point release (11.1.1.0.2 => 11.1.1.1.0) of JDeveloper. And while the change is only in the fourth digit, there are still quite a few very interesting new options, features and components new in this release. In this article a [...]

Using ADF Faces 11g Skinning for setting the styles of specific component instances or groups of instances

Skinning in ADF Faces is used to define user defined customizations with respect to one of the standard ADF skins. A skin is by and large a CSS document (CSS 3.0) that is interpreted at run time to generate the ‘real’ CSS documents (3.0 if that is what the browser can handle, 2.0 in most [...]

File upload/download and remote program execution using WS-Management – a practical solution

The previous blog post described a way to upload and (in theory at least) download text files to/from a remote Windows machine using WS-Management. In practice, the applicability of the method is  limited for upload (text files only, slow for large files) and almost nonexistent for download. Here is a much improved version.

This is another example of something that was too obvious for me to see last weekend when I was in the thick of fighting with WS-Management SOAP messages and learning about WMI classes. It just took a day of not thinking about it to have the solution pop in my mind: use ftp.exe. For the longest time (at least since Windows NT) Windows has been shipping with this FTP client. And the documentation shows that you can call it from the command line and provide it with the name of a text file containing the commands to execute. Bingo.

Specifically, here are the steps. Let’s say that I want to run a program called task.exe on a remote Windows machine and that program takes a large binary file (data.bin) as input. I want to transfer both to the remote machine and then run the program. This can be done in 3 simple steps:

Step 1: upload the FTP command file to the remote Windows machine. The content of the command file is below. mgmtserver.myco.com is the name of the machine from which the two files can be retrieved over FTP. I use anonymous FTP here, but you could just as well provide a username and password.

open mgmtserver.myco.com
anonymous
binary
get task.exe
get data.bin
quit

Step 2: execute the FTP commands above. This downloads task.exe and data.bin from mgmtserver.myco.com onto the remote Windows machine.

Step 3: execute the program on the remote Windows machine (”task.exe data.bin”).

Here are the on-the-wire messages corresponding to each step:

Step 1: upload the FTP command file to the remote Windows machine

<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope"
  xmlns:a="http://schemas.xmlsoap.org/ws/2004/08/addressing"
  xmlns:w="http://schemas.dmtf.org/wbem/wsman/1/wsman.xsd">
  <s:Header>
    <a:To>http://server:80/wsman</a:To>
    <w:ResourceURI s:mustUnderstand="true">http://schemas.microsoft.com/wbem/wsman/1/wmi/root/cimv2/Win32_Process </w:ResourceURI>
    <a:ReplyTo>
    <a:Address s:mustUnderstand="true">http://schemas.xmlsoap.org/ws/2004/08/addressing/role/anonymous</a:Address>
    </a:ReplyTo>
    <a:Action s:mustUnderstand="true">http://schemas.microsoft.com/wbem/wsman/1/wmi/root/cimv2/Win32_Process/Create</a:Action>
    <a:MessageID>uuid:9A989269-283B-4624-BAC5-BC291F72E854</a:MessageID>
  </s:Header>
  <s:Body>
    <p:Create_INPUT xmlns:p="http://schemas.microsoft.com/wbem/wsman/1/wmi/root/cimv2/Win32_Process">
      <p:CommandLine>cmd /c echo open mgmtserver.myco.com>ftpscript&amp;&amp;echo
      anonymous>>ftpscript&amp;&amp;echo binary>>ftpscript&amp;&amp;echo get
      task.exe>>ftpscript&amp;&amp;echo get data.bin>>ftpscript&amp;&amp;echo
      quit>>ftpscript</p:CommandLine>
      <p:CurrentDirectory>C:\data\winrm-test\</p:CurrentDirectory>
    </p:Create_INPUT>
  </s:Body>
</s:Envelope>

As before, you need to set the Content-Type HTTP header to “application/soap+xml;charset=UTF-8? (or UTF-16).

Step 2: execute the FTP commands to download the files from your server

It’s the same message, except the <p:CommandLine> element now has this value:

<p:CommandLine>ftp -s:ftpscript</p:CommandLine>

Step 3: execute the task.exe program on the remote Windows machine

Again, the same message except that the command line is simply:

<p:CommandLine>C:\data\winrm-test\task.exe data.bin</p:CommandLine>

Note that I have broken this down in three messages for clarity, but you can easily bundle all three steps in one SOAP message. Just use this command line:

<p:CommandLine>cmd /c echo open mgmtserver.myco.com>ftpscript&amp;&amp;echo
anonymous>>ftpscript&amp;&amp;echo binary>>ftpscript&amp;&amp;echo get
task.exe>>ftpscript&amp;&amp;echo get data.bin>>ftpscript&amp;&amp;echo
quit>>ftpscript&amp;&amp;ftp -s:ftpscript&amp;&amp;C:\data\winrm-test\task.exe
data.bin</p:CommandLine>

Of course this can also be used in reverse, to download files from the remote Windows machine rather than upload files to it. Just use PUT or MPUT as FTP commands instead of GET or MGET.

This mechanism is a major improvement, for many use cases, over what I originally described. I feel a bit like someone who just changed a flat tire by loosening the lug nuts with his teeth and then found the lug wrench under the spare tire.

Faster development of ADF applications - When to simply refresh the browser and when to redeploy the web application

ADF developers frequently are not exactly sure which changes they have applied to the application can be picked up in the running application, without redeploying the application, and which changes absolutely require a redeploy as they can not be added to a running application. Knowing when redeploy is not necessary - and increasing the number [...]

Querying Microsoft Office Access 2007 data from an Oracle 11g Database Part 4 A

Stage 4:  Automating Data Refreshes.

In part 1 of this blog, I demonstrated the initial configuration of the Oracle Database Gateway for ODBC (DG4ODBC).

Part 2 completed the configuration and also showed how to run some basic queries.

Part 3 showed a method of resolving issues encountered when dealing with LONG data type columns, using a combination of external tables and materialized views.

In the fourth and final part, I’ll demonstrate one method of automating the data refreshes for this solution. There are many solutions available to automate procesess, but for this example, I’ll use the database scheduler.
 
Step L: Check the objects we have created previously
——-
58) Run the query below to confirm the list of objects owned by NWIND_ACCESS, that were created in parts 1,2 and 3 of this blog:

conn nwind_access/zuggy4

select table_name from user_tables union select view_name from user_views;

The information below shows how the tables and views map to the Microsoft Office Access 2007 database tables from the northwind database.

ORCL11 Object                                            ORCL11 objects referenced                      Microsoft Office Access tables
==========                                           ======================            ====================
MV_NWIND_CUSTOMERS (materialized view)  NWIND_CUSTOMERS(external table)         Customers
MV_NWIND_ORDERS (materialized view)        NWIND_ORDERS (external table)             Orders
                                                                  V_ORDER_DETAILS(View)                       Order Details
                                                                  V_ORDER_STATUS(View)                        Order Status
                                                                  T_ORDER_DETAILS(Table)                      Order Details
                                                                  T_ORDER_STATUS(Table)                       Orders Status

Step M: Create Additional Materialized Views and re-create existing materialized views
——-

59) We created materialized views based on the Customers table and the Orders table from the northwind database. We now also need to create them for information stored within the Order Details and Orders Status tables.

As demonstrated previously, we aren’t able to base these on selects directly from the table. (If we try to create the materialized view as a “select * from v_order_details” or “select * from “Order Details”@Northwind” then you just see the error:  ORA-00942: table or view does not exist)

60) Create the text files that the external tables will be based on.

conn nwind_access/zuggy4

set head off feedback off trimspool on lines 32766 buffer 32767 pages 0 space 0 colsep ‘~’

alter session set nls_date_format=’DD-MON-YYYY HH24:MI:SS’;
spool
 C:\app\Administrator\admin\ORCL11\nwind_dump\order_details.txt
select * from
“Order Details”@Northwind;
spool off
spool
C:\app\Administrator\admin\ORCL11\nwind_dump\order_status.txt
select * from
“Orders Status”@Northwind;
spool off

Then re-set your SQLPLUS* environment:
 
set head on feedback on trimspool off lines 132 buffer 132 pages 1000 space 1 colsep ” “

Note: Throughout all of this work, ensure that the permissions of the directory which contains the output text files (i.e. the nwind_dir database directory location) is restricted according to your company’s security policies.
61) Create the new external tables nwind_order_details and nwind_order_status and re-create the existing external tables:

conn nwind_access/zuggy4

create table nwind_order_details
(
 ID                                                                       NUMBER(10),
 ORDER_ID                                                                 NUMBER(10),
 PRODUCT_ID                                                               NUMBER(10),
 QUANTITY                                                                 NUMBER(18,4),
 UNIT_PRICE                                                               NUMBER(19,4),
 DISCOUNT                                                                 FLOAT(53),
 STATUS_ID                                                                NUMBER(10),
 DATE_ALLOCATED                                                           DATE,
 PURCHASE_ORDER_ID                                                        NUMBER(10),
 INVENTORY_ID                                                             NUMBER(10)
)
organization external
(type oracle_loader
default directory nwind_dir
access parameters
(
records delimited by newline
badfile NWIND_DIR:’order_details.bad’
discardfile NWIND_DIR:’order_details.dsc’
logfile NWIND_DIR:’order_details.log’
fields terminated by ‘~’ optionally enclosed by ‘”‘ LRTRIM
missing field values are null
reject rows with all null fields
(
ID,
ORDER_ID, 
PRODUCT_ID, 
QUANTITY,    
UNIT_PRICE,             
DISCOUNT,                                          
STATUS_ID,                                                       
DATE_ALLOCATED  CHAR(20) date_format DATE mask “dd-mon-yyyy hh24:mi:ss”,                                                    
PURCHASE_ORDER_ID,                                                   
INVENTORY_ID                                                         
 )
)
location (NWIND_DIR:’order_details.txt’)
)
reject limit unlimited
/
create table nwind_order_status
(
STATUS_ID                                                                NUMBER(3),
STATUS_NAME                                                              VARCHAR2(100)
 )
organization external
(type oracle_loader
default directory nwind_dir
access parameters
(
records delimited by newline
badfile NWIND_DIR:’order_status.bad’
discardfile NWIND_DIR:’order_status.dsc’
logfile NWIND_DIR:’order_status.log’
fields terminated by ‘~’ optionally enclosed by ‘”‘ LRTRIM
missing field values are null
reject rows with all null fields
(
STATUS_ID,                                                             
STATUS_NAME                                                           
 )
)
location (NWIND_DIR:’order_status.txt’)
)
reject limit unlimited
/

Note: In the above, you’ll notice that we’ve left out the ” skip 1” clause that was included in the create table statements in earlier parts of this blog. As commands will not be echoed to the spool file, as we are using the database scheduler, we need to remove this clause, otherwise we’ll lose the first row of data. Because of this issue, we’ll also need to drop and re-create the existing Customers and Order external tables also. (We don’t need to re-create the materialized views that were based on these tables).  We also don’t need to include the ” load when ( != “off”)   ” commands in the access parameters section, as the SQL commands will not appear in the output file.

The Char: Myth busted or confirmed?

There are many myths on the Oracle database. Many of those myths were true statements versions ago, but became myths because the Oracle database developers outsmarted the limitations. There was a myth that said that you should put non-null columns in the beginning of the table and null columns at the end. This was because in earlier versions a null column in a row did not take space until there was a filled column after it. In that case the database preallocated space for the null column to save on re-allocation efforts. But I learned that the storing-techniques of the database were improved in a way that this statement became a myth. Although it is still a good practice to have a standard on organizing your tables.

I know quite a lot of the Oracle database. But most of it is from using the modern ones and carrying the lugage of courses in the older ones. I know how a car and it's engine theoretically work, but I have no up-to-date knowlegde of the internals of the modern car. In the same way I have to rely on what I hear from other experts and co-workers about statements as above. So I keep my ears open and carefull about spreading myths.

This week a valued co-worker came with a DDL-script to create a table with Char-columns. My first reaction was that he should replace the CHARs with VARCHAR2s. Since a Varchar, as suggested by it's name, should occupy space related to it's fillings, while a CHAR occupies space according to it's maximum size. Though this was true, I was not sure if this statement was passed over by the reality of the modern database's storage techniques.
But according to Tom Kyte, the statement still holds (article started in 2001, but is updated recently). However, browsing to the end of the article's list of comments, I read: if a char or a varchar2 column is null, than it does not take more room then is needed to hold the null-value.

So myth confirmed...

Approaches to “UPSERT”

This week in the Database Programmer we look at something called an "UPSERT", the strange trick where an insert command may magically convert itself into an update if a row already exists with the provided key. This trick is very useful in a variety of cases. This week we will see its basic use, and next week we will see how the same idea can be used to materialize summary tables efficiently.

An UPSERT or ON DUPLICATE KEY...

The idea behind an UPSERT is simple. The client issues an INSERT command. If a row already exists with the given primary key, then instead of throwing a key violation error, it takes the non-key values and updates the row.

This is one of those strange (and very unusual) cases where MySQL actually supports something you will not find in all of the other more mature databases. So if you are using MySQL, you do not need to do anything special to make an UPSERT. You just add the term "ON DUPLICATE KEY UPDATE" to the INSERT statement:

insert into table (a,c,b) values (1,2,3)
    on duplicate key update
     b = 2,
     c = 3

The MySQL command gives you the flexibility to specify different operation on UPDATE versus INSERT, but with that flexibility comes the requirement that the UPDATE clause completely restates the operation.

With the MySQL command there are also various considerations for AUTO_INCREMENT columns and multiple unique keys. You can read more at the MySQL page for the INSERT ... ON DUPLICATE KEY UPDATE feature.

A Note About MS SQL Server 2008

MS SQL Server introduced something like UPSERT in SQL Server 2008. It uses the MERGE command, which is a bit hairy, check it out in this nice tutorial.

Coding a Simpler UPSERT

Let us say that we want a simpler UPSERT, where you do not have to mess with SQL Server's MERGE or rewrite the entire command as in MySQL. This can be done with triggers.

To illustrate, consider a shopping cart with a natural key of ORDER_ID and SKU. I want simple application code that does not have to figure out if it needs to do an INSERT or UPDATE, and can always happily do INSERTs, knowing they will be converted to updates if the line is already there. In other words, I want simple application code that just keeps issuing commands like this:

INSERT INTO ORDERLINES
       (order_id,sku,qty)
VALUES 
       (1234,'ABC',5)

We can accomplish this by a trigger. The trigger must occur before the action, and it must redirect the action to an UPDATE if necessary. Let us look at examples for MySQL, Postgres, and SQL Server.

A MySQL Trigger

Alas, MySQL giveth, and MySQL taketh away. You cannot code your own UPSERT in MySQL because of an extremely severe limitation in MySQL trigger rules. A MySQL trigger may not affect a row in a table different from the row originally affected by the command that fired the trigger. A MySQL trigger attempting to create a new row may not affect a different row.

Note: I may be wrong about this. This limitation has bitten me on several features that I would like to provide for MySQL. I am actually hoping this limitation will not apply for UPSERTs because the new row does not yet exist, but I have not had a chance yet to try.

A Postgres Trigger

The Postgres trigger example is pretty simple, hopefully the logic is self-explanatory. As with all code samples, I did this off the top of my head, you may need to fix a syntax error or two.

CREATE OR REPLACE FUNCTION orderlines_insert_before_F()
RETURNS TRIGGER
 AS $BODY$
DECLARE
    result INTEGER; 
BEGIN
    SET SEARCH_PATH TO PUBLIC;
    
    -- Find out if there is a row
    result = (select count(*) from orderlines
                where order_id = new.order_id
                  and sku      = new.sku
               )

    -- On the update branch, perform the update
    -- and then return NULL to prevent the 
    -- original insert from occurring
    IF result = 1 THEN
        UPDATE orderlines 
           SET qty = new.qty
         WHERE order_id = new.order_id
           AND sku      = new.sku;
           
        RETURN null;
    END IF;
    
    -- The default branch is to return "NEW" which
    -- causes the original INSERT to go forward
    RETURN new;

END; $BODY$
LANGUAGE 'plpgsql' SECURITY DEFINER;

-- That extremely annoying second command you always
-- need for Postgres triggers.
CREATE TRIGGER orderlines_insert_before_T
   before insert
   ON ORDERLINES
   FOR EACH ROW
   EXECUTE PROCEDURE orderlines_insert_before_F();

A SQL Server Trigger

SQL Server BEFORE INSERT triggers are significantly different from Postgres triggers. First of all, they operate at the statement level, so that you have a set of new rows instead of just one. Secondly, the trigger must itself contain an explicit INSERT command, or the INSERT never happens. All of this means our SQL Server example is quite a bit more verbose.

The basic logic of the SQL Server example is the same as the Postgres, with two additional complications. First, we must use a CURSOR to loop through the incoming rows. Second, we must explicitly code the INSERT operation for the case where it occurs. But if you can see past the cruft we get for all of that, the SQL Server exmple is doing the same thing:

CREATE TRIGGER upsource_insert_before
ON orderlines
INSTEAD OF insert
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @new_order_id int;
    DECLARE @new_sku      varchar(15);
    DECLARE @new_qty      int;
    DECLARE @result       int;

    DECLARE trig_ins_orderlines CURSOR FOR 
            SELECT * FROM inserted;
    OPEN trig_ins_orderlines;

    FETCH NEXT FROM trig_ins_orderlines
     INTO @new_order_id
         ,@new_sku
         ,@new_qty;

    WHILE @@Fetch_status = 0 
    BEGIN
        -- Find out if there is a row now
        SET @result = (SELECT count(*) from orderlines
                        WHERE order_id = @new_order_id
                          AND sku      = @new_sku
                      )
    
        IF @result = 1 
        BEGIN
            -- Since there is already a row, do an
            -- update
            UPDATE orderlines
               SET qty = @new_qty
             WHERE order_id = @new_order_id
               AND sku      = @new_sku;
        END
        ELSE
        BEGIN
            -- When there is no row, we insert it
            INSERT INTO orderlines 
                  (order_id,sku,qty)
            VALUES
                  (@new_order_id,@new_sku,@new_qty)
            UPDATE orderlines

        -- Pull the next row
        FETCH NEXT FROM trig_ins_orderlines
         INTO @new_order_id
             ,@new_sku
             ,@new_qty;

    END  -- Cursor iteration

    CLOSE trig_ins_orderlines;
    DEALLOCATE trig_ins_orderlines;

END

A Vague Uneasy Feeling

While the examples above are definitely cool and nifty, they ought to leave a certain nagging doubt in many programmers' minds. This doubt comes from the fact that an insert is not necessarily an insert anymore, which can lead to confusion. Just imagine the new programmer who has joined the team an is banging his head on his desk because he cannot figure out why his INSERTS are not working!

We can add a refinement to the process by making the function optional. Here is how we do it.

First, add a column to the ORDERLINES table called _UPSERT that is a char(1). Then modify the trigger so that the UPSERT behavior only occurs if the this column holds 'Y'. It is also extremely import to always set this value back to 'N' or NULL in the trigger, otherwise it will appear as 'Y' on subsequent INSERTS and it won't work properly.

So our new modified explicit upsert requires a SQL statement like this:

INSERT INTO ORDERLINES
       (_upsert,order_id,sku,qty)
VALUES
       ('Y',1234,'ABC',5)

Our trigger code needs only a very slight modification. Here is the Postgres example, the SQL Server example should be very easy to update as well:

   ...trigger declration and definition above
   IF new._upsert = 'Y'
      result = (SELECT.....);
      _upsert = 'N';
   ELSE
      result = 0;
   END IF;
   
   ...rest of trigger is the same

Conclusion

The UPSERT feature gives us simplified code and fewer round trips to the server. Without the UPSERT there are times when the application may have to query the server to find out if a row exists, and then issue either an UPDATE or an INSERT. With the UPSERT, one round trip is eliminated, and the check occurs much more efficiently inside of the server itself.

The downside to UPSERTs is that they can be confusing if some type of explicit control is not put onto them such as the _UPSERT column.

Next week we will see a concept similar to UPSERT used to efficiently create summary tables.