7.6 Hiding the Import Procedure7.6.1 ProblemYou 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 SolutionThe 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 DiscussionUsing 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 . |