7.6 Hiding the Import Procedure

7.6.1 Problem

You are using the buffer-table framework for importing data, but you want to hide the import procedure from the user. You want users to load new data into a buffer table, but, from that point on, you want the rows to be processed automatically. You don't want the user to have to worry about initiating the validation procedure.

7.6.2 Solution

The solution here is to use a trigger to drive the validation process. As rows are loaded into the buffer table, an insert trigger will automatically validate those rows, and load them into the live production table. The following trigger will process new rows loaded into the BookstoreBuffer table:

CREATE TRIGGER UpdateBookstoreBuffer
ON BookstoreBuffer
FOR INSERT
AS BEGIN
   DECLARE @id UNIQUEIDENTIFIER
   
   DECLARE @BookId INTEGER
   DECLARE @Name CHAR(40)
   DECLARE @Quantity INTEGER
   DECLARE @Price DECIMAL(10,2)
   DECLARE @Type CHAR(20)

   DECLARE @CheckFlag INTEGER

   DECLARE BufferCursor CURSOR FOR
      SELECT Id, BookId, Name, Quantity, Price, Type 
      FROM BookstoreBuffer WHERE Status='I'
  
   OPEN BufferCursor  
   
   FETCH NEXT FROM BufferCursor 
      INTO @Id, @BookId, @Name, @Quantity, @Price, @Type

   WHILE @@FETCH_STATUS=0
   BEGIN
      /* Place validation code here. Set @checkFlag to 0
         for good records, and set it to 1 for bad records.
      */
      IF @quantity < 0
         SELECT @checkFlag=1
      ELSE 
         SELECT @checkFlag=0
     
      /* If the row passed validation, update the operational table.
      */
      IF @CheckFlag=0 BEGIN
         UPDATE Bookstore 
         SET 
            Name=@Name,
            Quantity=@Quantity,
            Price=@Price,
            Type=@Type
         WHERE bookID=@bookId

         IF @@ROWCOUNT=0  
            INSERT INTO Bookstore(BookId, Name, Quantity, Price, Type)
               VALUES(@BookId, @Name, @Quantity, @Price, @Type)
         UPDATE BookstoreBuffer SET Status='A' WHERE Id=@Id
      END ELSE 
         UPDATE BookstoreBuffer SET Status='R' WHERE Id=@Id

      FETCH NEXT FROM BufferCursor 
         INTO @Id, @BookId, @Name, @Quantity, @Price, @Type
   END

   CLOSE BufferCursor
   DEALLOCATE BufferCursor
END

The code in this trigger still expects the status flag of all newly inserted rows to be set to 'I'. You can see that the SELECT statement for the cursor named BufferCursor is restricted to those rows. New rows with a status of other than 'I' will still be inserted, but they will be ignored by this trigger. Remove the WHERE clause from the cursor's SELECT statement if you prefer not to worry about setting the status on newly inserted rows.

7.6.3 Discussion

Using a trigger as shown in this recipe is advantageous in terms of usability, because the user loading the data has one less thing to think about. When a procedure is used, as in the previous recipe, the load process looks like this:

This process isn't too bad, but if someone performs steps 1 and 3, skipping step 2, he might be misled into thinking that all their data had been successfully loaded when, in fact, it hadn't. Using a trigger to automatically process each row loaded into the buffer table leads to a two-step process that looks like this:

For the user doing the load, this is conceptually easier to deal with. Unfortunately, there is a potential performance penalty that may offset this gain in usability. When you use a stored procedure to validate newly loaded data, you control when that stored procedure is executed, and you can schedule it for a time during which the performance impact will be minimal. You also gain some advantages from processing data in bulk. When a trigger is used, each row must be validated at the moment it is inserted. If you are loading a large amount of data, this validation could impact the performance of your production system.

All in all, triggers improve usability; however, you have to carefully consider the possible performance consequences of a trigger-based solution. If performance is an issue and you want control over when data is moved from the buffer table to the production table, then use stored procedures. If your load volumes are low, and you don't expect performance to be an issue, then you may find using triggers to be more convenient.