Expressions and Computed Columns in Trigger

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Expressions and Computed Columns in Trigger

Author

Topic

vaidhiswaran
Starting Member

1 Post

Posted - 2006-09-28 : 01:14:50

Hi Friends,

These r Few lines from Sql Server Books Online

=====================================================================Views can contain expressions in their select list that do not map to any base table columns, for example:

CREATE VIEW ExpressionView
AS
SELECT *, GETDATE() AS TodaysDate
FROM Northwind.dbo.Employees

Although the TodaysDate column does not map to any table column, Microsoft® SQL Server™ 2000 must build a TodaysDate column in the inserted table it passes to an INSTEAD OF trigger defined on ExpressionView. The inserted.TodaysDate column is nullable, however, so an INSERT referencing ExpressionView does not have to supply a value for this column.
=====================================================================

as per the above lines the following stmt should work

insert into ExpressionView(Columns in Emloyee Table) values(Values For the Employee Table)

it Gives the error msg

Server: Msg 233, Level 16, State 2, Line 1
The column 'TodaysDate' in table 'Expressionview' cannot be null.

Plz Help me.....

Thanks

vaidhiswaran

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-28 : 04:45:08

I don't know the underlying reason..but it's obvious from the behavior that computed column which it creates is not nullable. If you want to achieve it that way, you have to do a little trick to make the computed column nullable. Here is one way:

CREATE VIEW ExpressionView
AS
SELECT *, Nullif(GETDATE(), NULL) AS TodaysDate
FROM Northwind.dbo.Employees

Harsh Athalye
India.
"Nothing is Impossible"

Kristen
Test

22859 Posts

Posted - 2006-09-28 : 05:09:57

Not much help, but I often seem to have problems inserting into views anyway, so I avoid doing it!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources