Section 7.6. Hiding the Import Procedure

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:

Load data into buffer table
Invoke stored procedure to process the newly loaded data
Check for rejected records

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:

Load data into buffer table
Check for rejected records

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 .