• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 417
  • Last Modified:

Instead of triggers on Views (won't fire)

I need help getting my "Instead Of" triggers to fire on views.

I have inherited the following table (don't ask):

CREATE TABLE [dbo].[data_value] (
     [table_id] [int] NOT NULL ,
     [column_id] [char] (4) NOT NULL ,
     [key_id] [varchar] (15) NOT NULL ,
     [data_value_t] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO

To simplify management of these values, I have created a series of views similar to the following. Each view basically isolates one of these "meta-tables".

CREATE VIEW dbo.meta_Calculation_Code
AS
SELECT  key_id,
     MAX(CASE WHEN column_id = '1' THEN dbo.data_value.data_value_t END) AS Code,
     MAX(CASE WHEN column_id = '2'
          THEN data_value.data_value_t END) AS Description,
     MAX(CASE WHEN column_id = '3'
          THEN data_value.data_value_t END) AS Calculation
FROM     dbo.data_value
WHERE     table_id = 51
GROUP BY     key_id

Then, I created Instead-Of triggers on each view to maintain the base table.

CREATE TRIGGER tr_ins_Calculation_Code ON [dbo].[meta_Calculation_Code]
Instead of INSERT
AS

BEGIN
     --Code column
     insert into data_value
     select      51 as table_id,
          '1' as column_id,
          inserted.key_id,
          inserted.Code as data_value_t
     from inserted
     
     --Description column
     insert into data_value
     select      51 as table_id,
          '2' as column_id,
          inserted.key_id,
          inserted.Description as data_value_t
     from inserted
     
     --Calculation column
     insert into data_value
     select      51 as table_id,
          '3' as column_id,
          inserted.key_id,
          inserted.Calculation as data_value_t
     from inserted
END

The triggers work fine as long as I have an aggregate function in the view. However, most of the tables only have one column, so I have adjusted most of the views to look more like the following for better performance.

CREATE VIEW dbo.meta_Investor
AS
SELECT     key_id, data_value_t AS InvestorName
FROM         dbo.data_value
WHERE     (table_id = 6)

NOW THE PROBLEM: When there is no aggregate function in the view, SQL Server tries to update the table directly BEFORE it calls my Instead-Of triggers. This creates a problem on inserts because my trigger supplies the table_id and column_id values. The data_value table won't accept the insert without these values, so I get a "Can't insert NULL" error.

Does anyone know how to force SQL Server to use the instead-of trigger without unnecessary aggregate functions?
0
jason_lewis
Asked:
jason_lewis
  • 6
  • 3
  • 3
  • +2
1 Solution
 
Scott PletcherSenior DBACommented:
According to BOL:
"
An INSERT statement referencing a view that has an INSTEAD OF INSERT trigger must supply values for every view column that does not allow nulls.
...
The INSERT statement can generate dummy values for these types of columns.
...
The INSTEAD OF trigger can ignore the value supplied when it forms the INSERT statement that inserts the values into the base table.
"

So, you will either have to include table_id and column_id in the view and provide dummy values for them or simply remove the NOT NULL restriction.  If you are always using a trigger to insert these values, you should be able to safely make then NULLable knowing that the trigger will provide a proper value prior to the actual insert.
0
 
Scott PletcherSenior DBACommented:
I don't think SQL is doing the INSERT first; rather, it is checking the NOT NULL restriction prior to giving your INSTEAD OF trigger control.
0
 
spcmnspffCommented:
Since there are no joins in the view, can you create the triggers on the table rather than the view.  I've never seen an instead of trigger behave this way before, but then again I don't recall ever compiling one on a view before either.  It seems to me that the advantage of creating the trigger on the view is that you can involve mutiple tables in a join, which doesn't apply here, or you can control when the trigger is fired by inserting into the view or the table.  

Otherwise a table compiled on a trigger should fire whether inserting into the table or inserting into the view into the table an insert still occurs to the underlying table . . .
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jason_lewisAuthor Commented:
Scott --

I always thought that statement meant that IF you choose an identity, timestamp, or calculated columns in your view, then you had to supply dummy values for those. I didn't think it meant you had to use EVERY non-nullable field from the base table in your view.

But I went ahead and tried your suggestion, anyway. No luck.

SQL Server just tries to insert the "Dummy" values, even though I do assign the correct values in the trigger.

The problem is that the trigger never fires. SQL Server is sending the data straight to the base table without using the trigger. I even tried inserting a call to raiserror on the very first line of the trigger. The error never gets raised, so the trigger just isn't running.

Please note -- as to your second comment, SQL server IS trying to insert the data. I get a foreign_key contraint error on the dummy values.
0
 
Scott PletcherSenior DBACommented:
That's intesting.  I agree with your first paragraph and had always thought so too but thought because of the problem you were having that maybe it was requiring all.

I've got much more experience with AFTER INSERT triggers, and, given that this is MS code, maybe there is a bug that prevents the trigger from firing properly.

Can you verify that the trigger's been successfully created and on the appropriate SQL Server instance and db?  For example, does "EXEC sp_helptrigger view_name" show the trigger properly?
0
 
jason_lewisAuthor Commented:
Scott --

I always thought that statement meant that IF you choose an identity, timestamp, or calculated columns in your view, then you had to supply dummy values for those. I didn't think it meant you had to use EVERY non-nullable field from the base table in your view.

But I went ahead and tried your suggestion, anyway. No luck.

SQL Server just tries to insert the "Dummy" values, even though I do assign the correct values in the trigger.

The problem is that the trigger never fires. SQL Server is sending the data straight to the base table without using the trigger. I even tried inserting a call to raiserror on the very first line of the trigger. The error never gets raised, so the trigger just isn't running.

Please note -- as to your second comment, SQL server IS trying to insert the data. I get a foreign_key contraint error on the dummy values.
0
 
jason_lewisAuthor Commented:
Scott --

I always thought that statement meant that IF you choose an identity, timestamp, or calculated columns in your view, then you had to supply dummy values for those. I didn't think it meant you had to use EVERY non-nullable field from the base table in your view.

But I went ahead and tried your suggestion, anyway. No luck.

SQL Server just tries to insert the "Dummy" values, even though I do assign the correct values in the trigger.

The problem is that the trigger never fires. SQL Server is sending the data straight to the base table without using the trigger. I even tried inserting a call to raiserror on the very first line of the trigger. The error never gets raised, so the trigger just isn't running.

Please note -- as to your second comment, SQL server IS trying to insert the data. I get a foreign_key contraint error on the dummy values.
0
 
jason_lewisAuthor Commented:
Sorry, I always forget that refresh keeps posting the previous comment.
0
 
jason_lewisAuthor Commented:
spcmnspff --

The whole reason I want the views is so I don't have to deal with the base table directly. I don't want to have to figure out the proper "table_id" and "column_id" every time I need to change a value, design a report, etc.

So I'm not sure that a trigger on the base table would help.

For example, the following "record" (from the view):

key_id     Code          Description     Calculation
1     TRANSAMT     Escrow Advance     msp_transaction

is actually stored in the base table as:

table_id      column_id     key_id     data_value
51          1             1     TRANSAMT
51          2             1     Escrow Advance
51          3             1     msp_transaction


As much as I want to, I can't just change the base table because it would break the whole application. I thought updateable views would be a nice way to transition the app slowly without breaking everything all at once.


Scott -- sp_helptrigger does return the trigger with isinsert=1 and isinsteadof = 1.
0
 
spcmnspffCommented:
How about staging tables for the inserts?  Your tables can remain empty but reflect the schema you want.  Then an istead of trigger on the  staging table does the insert into the base table computing the Col_ID and table_ID.  The only headache now is that you have to use the view for the select and the empty staging tables for inserts.  Just a thought . . . =)
0
 
checooCommented:
Hi,

If you want the instead of trigger to fire without inserting dummy values in the not null columns. Try something like the following (make your views partioned)

Eg..

Create table test
(
 a int not null,
 b int,
 c varchar(10) not null)

Create view TestView as
Select a, b, c from Test
Union
Null, null, Null

Then your instead of trigger will work.

I hope this help.
Cheers
0
 
mironCommented:
this code worked on MSDE 2000
here is the result of select @@version
Microsoft SQL Server  2000 - 8.00.384 Desktop Engine
with result
__________________________________________
table_id    column_id key_id  data_value_t
6           1         31              user
6           2         31              user
6           3         31              user
6           1         32              user
6           2         32              user
6           3         32              user

CREATE TABLE [dbo].[data_value] (
    [table_id] int NOT NULL ,
    [column_id] char(3) NOT NULL,
    [key_id] varchar(15) NOT NULL ,
    [data_value_t] varchar(50) NOT NULL
) ON [PRIMARY]
GO

CREATE VIEW dbo.meta_Investor ( key_id, InvestorName)
AS
SELECT     key_id, data_value_t AS InvestorName
FROM       dbo.data_value
WHERE     (table_id = 6)
GO

CREATE TRIGGER tr_ins_Calculation_Code ON dbo.meta_Investor
Instead of INSERT
AS
BEGIN
    --Code column
    insert into data_value ( table_id, column_id, key_id, data_value_t )
    select      
     6,
        '1',
        key_id,
        InvestorName
    from inserted
   
    --Description column
    insert into data_value  ( table_id, column_id, key_id, data_value_t )
    select
     6 as table_id,
        '2',
        key_id,
        InvestorName
    from inserted
   
    --Calculation column
    insert into data_value  ( table_id, column_id, key_id, data_value_t )
    select
     6 as table_id,
        '3' as column_id,
        key_id,
        InvestorName
    from inserted
END
GO

insert into dbo.meta_Investor( key_id, InvestorName ) select '31', 'user'
insert into dbo.meta_Investor( key_id, InvestorName ) select '32', 'user'
select * from data_value

drop trigger [tr_ins_Calculation_Code]
drop view    [dbo].[meta_Investor]
drop table   [dbo].[data_value]
0
 
jason_lewisAuthor Commented:
spcmnspff -- That's a great idea. I could even maintain triggers to keep the tables in sync. I will post another 100 points for you. However...

miron -- You get the points for this question. Although your post was almost identical to my original, it was just different enough to make me realize how stupid I was being. The problem was not with the trigger, at all. The problem was with Enterprise Manager, which doesn't surprise me. As long as I use an insert statement from Query Analyzer (like you did), the trigger fires and everything is perfect. I was opening the view in EM and trying to add a row from there. I guess that's what I get for being lazy.

Thanks, everyone!
0
 
spcmnspffCommented:
Hahaha . . . and all along . . .   Yeah somehow EM can manage to get circumvent things like check constraints and triggers at times.  Well I'm glad the issue was resolved.  Thanks for the points . . . =D
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 6
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now