Solved

Instead of triggers on Views (won't fire)

Posted on 2002-06-11
14
390 Views
Last Modified: 2012-08-14
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
Comment
Question by:jason_lewis
  • 6
  • 3
  • 3
  • +2
14 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 7071120
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 7071123
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
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7071187
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
 
LVL 1

Author Comment

by:jason_lewis
ID: 7071194
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 7071227
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
 
LVL 1

Author Comment

by:jason_lewis
ID: 7071229
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
 
LVL 1

Author Comment

by:jason_lewis
ID: 7071251
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 1

Author Comment

by:jason_lewis
ID: 7071252
Sorry, I always forget that refresh keeps posting the previous comment.
0
 
LVL 1

Author Comment

by:jason_lewis
ID: 7071258
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
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7071857
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
 
LVL 9

Expert Comment

by:checoo
ID: 7071996
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
 
LVL 9

Accepted Solution

by:
miron earned 100 total points
ID: 7072304
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
 
LVL 1

Author Comment

by:jason_lewis
ID: 7073141
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
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7073159
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now