INSTEAD-OF Trigger on IDENTITY Column

Question

Post reply

INSTEAD-OF Trigger on IDENTITY Column

cwedgwood50

SSCommitted

Points: 1751
More actions
August 28, 2003 at 7:57 am

#159590

Hi,
I have the following INSTEAD-OF trigger on my view:
CREATE TRIGGER [TR_Testing_I] ON dbo.VIEWTesting_META
INSTEAD OF INSERT
AS
--Perform the insert in code explicitly
INSERT INTO TBL_Testing
SELECT testdesc
FROM inserted
The first field in the view is the PK of the underlying table, which is set as an autoincrementing identity integer column. If I add a new record to the table I do not need to specify a value for this field as the system allocates the next available number.
When I try to do this through the view it will not let me leave the column empty saying,
"The Column 'testingID' in table prototype2.dbo.Viewtesting_meta' cannot be null"
If I manually put a value into this field in EM then it gets replaced by the system generated autonumber.
So I tried to have the insert in the trigger include a dummy value in the trigger.
INSERT INTO TBL_Testing (testingID,testdesc) Values(1,'testing')
But then I get a cannot insert an explicit value for identity column when IDENTITY_INSERT is off, error.
How can I get this to work for the identity column.
Thanks in advance
Chris

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

fromnaija SSCrazy Points: 2876 More actions · Answer 1

fromnaija

SSCrazy

Points: 2876

August 28, 2003 at 8:19 am

#471830

Could you post your view creation code??

Joseph

cwedgwood50 SSCommitted Points: 1751 More actions · Answer 2

Hi,

The view was created like this:

CREATE VIEW dbo.VIEWTesting

WITH VIEW_METADATA

AS

SELECT *

FROM dbo.TBL_Testing

Thanks

Chris

brokenrulz SSCrazy Points: 2261 More actions · Answer 3

quote:
Hi,
When I try to do this through the view it will not let me leave the column empty saying,
"The Column 'testingID' in table prototype2.dbo.Viewtesting_meta' cannot be null"

You have to pass values for all the columns when you are updating through the view. These values will be dummy and will be ignored when the instead of trigger on the view fires. Since the instead of trigger does not refer these columns.Please let me know If I am clear

Pay Respect to People on your way up. For you will meet the same People on your way down.

He who knows others is learned but the wise one is one who knows himself.

brokenrulz SSCrazy Points: 2261 More actions · Answer 4

quote:
Hi,
When I try to do this through the view it will not let me leave the column empty saying,
"The Column 'testingID' in table prototype2.dbo.Viewtesting_meta' cannot be null"

You have to pass values for all the columns when you are updating through the view. These values will be dummy and will be ignored when the instead of trigger on the view fires. Since the instead of trigger does not refer these columns.Please let me know If I am clear

Pay Respect to People on your way up. For you will meet the same People on your way down.

He who knows others is learned but the wise one is one who knows himself.

cwedgwood50 SSCommitted Points: 1751 More actions · Answer 5

Hi brokenrulz

Thanks for answering.

That works fine in EM, but my users do not have access through em to put in dummy values. I am using a bound form in an MSAccess .adp file to allow users to add/alter data. This reports the field correctly as autonumber in access, and so does not pass any dummy values.

The only way I can see is to take off the identity from the underlying table and use the trigger to generate a uniqueID for the PK, but I would prefer not to need to do this. Is there a way around this?

Thanks

Chris

fromnaija SSCrazy Points: 2876 More actions · Answer 6

If you are not using the view to select the primary key column, you could remove that from the view definition like so:

<code>

ALTER VIEW dbo.VIEWTesting

WITH VIEW_METADATA

AS

SELECT testdesc

FROM dbo.TBL_Testing

</code>

This will allow you to insert your data and the identity column in the underlining table will be automatically filled as well.

Cheers.

Joseph

cwedgwood50 SSCommitted Points: 1751 More actions · Answer 7

Hi fromnaija,

The users need to be able to see the value of the PK, but not alter it. So I must inculde it in the view. I think maybe I could make another form just for adding values and do it your way from there, but it would have been nice not to need to. Guess this is an MSAccess issue tho'.

Thanks For your help

Cheers

Chris

DavidWalker Mr or Mrs. 500 Points: 508 More actions · Answer 8

CREATE TRIGGER [TR_Testing_I] ON dbo.VIEWTesting_META

INSTEAD OF INSERT

AS

--Perform the insert in code explicitly

INSERT INTO TBL_Testing

(testdesc) --- shouldn't this be specified?

SELECT testdesc

FROM inserted

Not sure whether the above fixes the problem. As well, I avoid using * in view creation.

eletuw SSCrazy Points: 2464 More actions · Answer 9

I once had a similar problem using MS Access. I solved the problem by reading what the BOL had to say about triggers and identity column. I am sure you will get a solution after reading the BOL.

cwedgwood50 SSCommitted Points: 1751 More actions · Answer 10

Thanks everyone for their suggestions.

After further investigation I am still no closer to being able to use an Access .adp bound form to insert into a view that has an instead-of trigger set and where the PK must be included on the form. I don't think it can be done!

Cheers

Chris