Foreign Key Row Creation in Triggers

• 2012年7月6日 8:49

   

   
  

  0
  This is about a Data Warehouse application I am developing. I think that that the more I could relate tables via foreign keys, the better the database would be.
  My OLTP cannot be relied upon for such integrity, so I took the step of, if the foreign keys table row is missing, I just create it with default values in non-key fields. So if I am doing an insert, then I have all the columns available to me to check whether a foreign key table row exists, and if it doesn't, create it. I opted to do this with instead-of triggers and they looked like this:
  ALTER TRIGGER [OLTP].[Trigger_Sub Ledger Balances] on
  [OLTP].[Sub Ledger Balances]
  INSTEAD OF INSERT, UPDATE AS
  Begin
  IF (UPDATE([Company Number]) OR UPDATE([S/L Number]))
  Begin
  INSERT INTO OLTP.[Sub Ledger Master] ([Company Number], [Sub Account Code], [UTYP])
  Select DISTINCT [Company Number], [S/L Number], 'T'
  from inserted i
  where i.[Company Number] is not null and i.[S/L Number] is not null
   and not exists
  (Select 1 from OLTP.[Sub Ledger Master] f where
  f.[Company Number] = i.[Company Number] and f.[Sub Account Code] = i.[S/L Number])
  End
  ... then all of the other FK tables followed by the insert or update to the table that the trigger is over:
  IF NOT EXISTS (SELECT * FROM DELETED)
  Begin
  INSERT INTO OLTP.[Sub Ledger Balances](
  ,[Company Number]
  ,[S/L Number]
  ,[G/L Number]
  ,[Fiscal Year Of Budget]
  ,[Opening Balance]
  ... Select w,x,y,z from inserted
  
  It is important to note is that if [Sub Ledger Master] also has a trigger, then we are nesting triggers.
  This all worked very well while I was still developing the "full database load" part of the project, but when I got to the incremental data load, I chose to use cursor processing to handle adds, changes and deletes to a table. It still works OK. But it is slow and this is because of the insert into foreign key table having to occur for every row. It is four times faster without the foreign key table inserts and this is important.
  
  So I turned the trigger on its head and did the Insert, into the table that the trigger is over, first, in a Try block with the insert into foreign key table in the corresponding Catch block. But if the Try went wrong, it seems I have to use XACT_STATE() = -1 in the catch to determine this and do a rollback transaction and this is where the problems start.
  
  I have had a variety of errors and hung SSMS too and it would take too long and be too detailed to go into these.
  
  So the question is: has anyone got experience of doing something similar who could give me guidance on how to go about this.
  
  Thanks in advance,
  Frank
  
  
  • 回复
  • 引用