Non-null on view column - SQL Server Forums

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)

Non-null on view column

Author

Topic

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2005-04-29 : 13:51:28

Hi,

I have a view with a column who's value is retrieved using a sub-query.

I'm going to make the view updatable using an instead of trigger.

But, when I look at the view's columns in Query Analyzer, it says that this derived column allows nulls. Is there a way for me to declare the column as 'not null'?

Cheers,

XF.

X002548
Not Just a Number

15586 Posts

Posted - 2005-04-29 : 14:51:23

maybe if you post he view and the trigger....seems pretty bizarre to me..

Brett

8-)

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2005-04-29 : 14:53:53

What seems bizarre?

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2005-04-29 : 15:18:49

Anyhow... here's the view:


SELECT AddressID, Description, Image,

(CASE
	WHEN (SELECT AddressID FROM tbl_Guide WHERE AddressID = e.AddressID) IS NOT NULL THEN Cast(1 as bit)
	ELSE Cast(0 as bit)
END
) as 'InGuide'

FROM  dbo.tbl_estateagent e

And here is an insert trigger on the view:


ALTER   TRIGGER [EstateAgentGuide] ON [dbo].[EstateAgent_InGuide] 
INSTEAD OF UPDATE 
AS

SET XACT_ABORT ON

BEGIN TRANSACTION

DECLARE @AddressID int
SET @AddressID = (SELECT AddressID FROM inserted)

UPDATE
	tbl_estateagent
SET
	Description = (SELECT Description FROM inserted),
	Image = (SELECT Image FROM inserted)
WHERE
	AddressID = @AddressID

IF UPDATE(InGuide)
BEGIN

DECLARE @in bit
SET @in = (SELECT InGuide FROM inserted)

IF @in = 1
BEGIN
	INSERT INTO tbl_Guide
		(AddressID, CategoryID)
	VALUES
		(@AddressID, 133)
END
ELSE
BEGIN
	DELETE
		tbl_Guide
	WHERE
		AddressID = @AddressID



END

END



COMMIT TRANSACTION

Also, the AddressID column is the primary key for tbl_estateagent plus a foreign key to a table of addresses. (Its a 1:1 relationship.)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-29 : 15:21:08

I think if you wrap your column definition (the sub-query) in a IsNull function the resulting view column isnullable property will be no.

ie:
GO
create view myView as select col1 = isNull((select count(*) from sysObjects),0)
GO

Be One with the Optimizer
TG

X002548
Not Just a Number

15586 Posts

Posted - 2005-04-29 : 15:42:01

What are you gonna fo with the computed Columns?


USE Northwind
GO

CREATE VIEW myView99
AS
	SELECT o.OrderId, o.ShipName, d.ProductId, Total=Quantity*UnitPrice 
	  FROM Orders o 
    INNER JOIN [Order Details] d 
	    ON o.OrderId = d.OrderId
GO

CREATE TRIGGER myTrigger99 ON myView99 INSTEAD OF UPDATE
AS
	UPDATE o 
	   SET ShipName  = i.ShipName 
	  FROM Orders o 
    INNER JOIN inserted i 
	    ON o.OrderId = i.OrderId

	UPDATE d 
	   SET ProductId = i.ProductId, Quantity = 1, UnitPrice = i.Total 
	  FROM [Order Details] d 
    INNER JOIN inserted i 
	    ON d.OrderId = i.OrderId
GO


SELECT TOP 10 * FROM myView99 ORDER BY OrderId DESC
GO

SET NOCOUNT ON
DECLARE @OrderId int
INSERT INTO Orders(ShipName) SELECT 'Kaiser Kites'
     SELECT @OrderId=SCOPE_IDENTITY()
INSERT INTO [Order Details](OrderId, ProductId, Quantity, UnitPrice)
     SELECT @OrderId, 11, 10, 100
     SELECT * FROM myView99 WHERE OrderId = @OrderId
     UPDATE myView99 SET ProductId = 42 WHERE OrderId = @OrderId 
     SELECT * FROM myView99 WHERE OrderId = @OrderId
     UPDATE myView99 SET Total = 10000 WHERE OrderId = @OrderId 
     SELECT * FROM myView99 WHERE OrderId = @OrderId
     SELECT * FROM Orders WHERE OrderId = @OrderID
     SELECT * FROM [Order Details] WHERE OrderId = @OrderID

DELETE FROM [Order Details] WHERE OrderId = @OrderId
DELETE FROM Orders WHERE OrderId = @OrderId
GO

SET NOCOUNT OFF
DROP TRIGGER myTrigger99
DROP VIEW myView99
GO

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2005-04-29 : 15:54:49

Well...you got a bigger problem...


SET @AddressID = (SELECT AddressID FROM inserted)

You're not thinking set based..inserted may have many rows...not just 1

And why is the derived column not a left join instead?

Brett

8-)

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2005-04-30 : 15:05:38

Thanks for your replies. I thought of a much better way of doing this so I can forget all this complexity.

Just make the derived column a concrete column in the base table.
Then I can have one view something like this...

(SELECT AddressID, CategoryID FROM tbl_Guide)
UNION
(SELECT AddressID, 133 FROM tbl_estateagent WHERE InGuide = 1)

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2005-04-30 : 15:08:14

quote:
And why is the derived column not a left join instead?

Why would that be better?

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2005-05-01 : 12:14:57

quote:

Well...you got a bigger problem...

SET @AddressID = (SELECT AddressID FROM inserted)

You're not thinking set based..inserted may have many rows...not just 1

If a trigger is only for inserts, is it possible to have more than one row in the inserted table? I think this becuase surely you can only INSERT one row per transaction.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-01 : 12:33:26

quote:

If a trigger is only for inserts, is it possible to have more than one row in the inserted table? I think this becuase surely you can only INSERT one row per transaction.

Wrong!!!!!

A lot of programmers unfortunately make this mistake. The inserted table holds ALL rows inserted on any given transaction. So, if you run an INSERT statement that inserts 10 million rows, your trigger only worked for ONE of those rows. That's a data integrity nightmare. I can't tell you how many times I've had to rip through databases and fix this error. Unfortunately, it's always too late to fix any of the data they screwed up.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2005-05-01 : 13:09:16

Gee whizz, thanks for that tip then!

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2005-05-01 : 13:15:08

Please let me get this clear...

Let table1 have an insert trigger.

So for...


INSERT table1 ( column1 ) VALUES ( 1 )
INSERT table1 ( column1 ) VALUES ( 1 )

..the trigger fires twice?

What about


BEGIN TRANSACTION

INSERT table1 ( column1 ) VALUES ( 1 )
INSERT table1 ( column1 ) VALUES ( 1 )

COMMIT TRANSACTION

Perhaps here the trigger would fire once?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-01 : 13:46:54

The case arises when You do:
INSERT table1( column1 )
SELECT 1 UNION ALL SELECT 2 -- Two rows inserted, trigger fires once.

rockmoose

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2005-05-01 : 14:35:02

Yes I see. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources