Instead of Trigger an update of multiple tables

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

Old Forums

CLOSED - General SQL Server

Instead of Trigger an update of multiple tables

Author

Topic

jdmarsh2g
Starting Member

4 Posts

Posted - 2007-03-12 : 16:09:22

Why am I getting this error on an identity insert..... ?
Of course I had to leave this code for several days and I know that I was able to get an insert into the servers table that has an identity column through a view but now I get this error
when trying to insert this into a view......

The column 'server_id' in table 'serverapps_vw' cannot be null.
Its been a song and dance for the longest.......
My goals is to insert a record through a view that has multiple base tables underlying the view.................

ALTER TRIGGER [tr_InsServerApps] ON [dbo].[serverapps_vw]
INSTEAD OF INSERT
AS

--declare @server_id int
--declare @apps_id int

BEGIN
SET NOCOUNT ON

SET IDENTITY_INSERT servers ON
--SET IDENTITY_INSERT appsdetails ON

IF (NOT EXISTS (SELECT
s.server_id
FROM
servers s, inserted i
WHERE
s.server_id = i.server_id))

INSERT INTO dbo.servers
(
server_name,
criticality,
environment
)

SELECT
server_name,
criticality,
environment
FROM
inserted

END

SET IDENTITY_INSERT servers OFF
--SET IDENTITY_INSERT appsdetails OFF

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-12 : 17:21:43

You're turning identity insert on but then you aren't specifying a value for the server_id column (I'm assuming this is the identity column in the table). If you turn identity insert on, you have to specify a value for the identity column.

Kristen
Test

22859 Posts

Posted - 2007-03-12 : 22:31:23

This looks a bit dangerous:


IF (NOT EXISTS (SELECT 
s.server_id
FROM
servers s, inserted i
WHERE
s.server_id = i.server_id))

if multiple records are inserted and ONE of them exists then NONE of the rest will be inserted. Better to put this in the INSERT I reckon


INSERT INTO dbo.servers
(
server_name,
criticality,
environment
)

SELECT
server_name,
criticality,
environment
FROM
inserted
WHERE NOT EXISTS
(
    SELECT s.server_id
    FROM   servers s
    WHERE  s.server_id = i.server_id
)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources