Cannot insert/update data in view? 1

[sparkbyte]

I am trying to split some data from the Employee table for future expansion and security.

Current table

tblEmployee
EmployeePK,
EmployeeID, ' this is the users Network Logon ID.
EmployeeFN,
EmployeeMI,
EmployeeLN,
EmployeeDept


New Tables

tblEmployees
EmployeeID_PK
EmployeeFN
EmployeeMI
EmployeeLN
EmployeeDept

tblEmployeeNetAccts
EmployeeID_PK
EmployeeDomainID
EmployeeFIPSID

tblEmployees.EmployeeID_PK --> One-to-One --> tblEmployeeNetAccts.EmployeeID_PK
Cascade Inserts and Deletes

Created a view as

SELECT     dbo.tblEmployee.EmployeeFN, dbo.tblEmployee.EmployeeMI, dbo.tblEmployee.EmployeeLN, dbo.tblEmployee.EmployeeDept, 
                      dbo.tblEmployeeNetAccts.EmployeeDomainID, dbo.tblEmployeeNetAccts.EmployeeFIPSID
FROM         dbo.tblEmployee INNER JOIN
                      dbo.tblEmployeeNetAccts ON dbo.tblEmployee.EmployeeID_PK = dbo.tblEmployeeNetAccts.EmployeeID_PK

But when trying to add a row. I get the following error.

No row updated
The data in row 62 was not committed.
Error Source: .Net SqlClient Data Provider.
Error Message: View or function 'View_Employees' is not updatable because the modification afffects multiple base tables.
Correct the errors and retry or rpess ESC to cancel the change(s).

Could someone please give some insight on how this all works and where I should be looking to accomplish what i would like to do.

Thanks!!


Thanks

John Fuhrman

 

[RiverGuy]

Write an INSTEAD OF trigger. Look it up on MSDN/Books Online.

 

[sparkbyte]

OK, read what they are and went through a short tutorial.

I think this should be close, but I get an error on the second insert.

Cannot insert null value into the ID field.

This has to be fairly close.

CREATE VIEW [dbo].[View_Employees]
AS
	SELECT     
		dbo.tblEmployee.EmployeeID_PK, 
		dbo.tblEmployee.EmployeeFN, 
		dbo.tblEmployee.EmployeeMI, 
		dbo.tblEmployee.EmployeeLN, 
		dbo.tblEmployee.EmployeeDept, 
		dbo.tblEmployeeNetAccts.EmployeeDomainID, 
		dbo.tblEmployeeNetAccts.EmployeeFIPSID
	FROM
		dbo.tblEmployee 
	INNER JOIN dbo.tblEmployeeNetAccts ON 
		dbo.tblEmployee.EmployeeID_PK = dbo.tblEmployeeNetAccts.EmployeeID_PK

CREATE TRIGGER dbo.trg_AddEmployee On View_Employees
Instead Of Insert
As
Begin
	If Exists
	(
		Select Top 1 *
		From Inserted
	)
	Begin
		Insert Into dbo.tblEmployee
		(
			EmployeeID_PK,
			EmployeeFN, 
			EmployeeMI, 
			EmployeeLN, 
			EmployeeDept
		)
		Select
			NewID(),
			i.EmployeeFN, 
			i.EmployeeMI, 
			i.EmployeeLN, 
			i.EmployeeDept
		From Inserted i
;
		Insert Into dbo.tblEmployeeNetAccts
		(
			EmployeeID_PK,
			EmployeeDomainID,
			EmployeeFIPSID
		)
		Select 
			i.EmployeeID_PK,
			i.EmployeeDomainID,
			i.EmployeeFIPSID
		From Inserted i
	End
End

insert into dbo.View_Employees
	(EmployeeFN, EmployeeMI, EmployeeLN, EmployeeDept, EmployeeDomainID, EmployeeFIPSID)
Values
	('Test','N','Dude','IT','TNDude','')

(1 row(s) affected)
[red]Msg 515, Level 16, State 2, Procedure trg_AddEmployee, Line 27
Cannot insert the value NULL into column 'EmployeeID_PK', table 'MailroomTracking2.dbo.tblEmployeeNetAccts'; column does not allow nulls. INSERT fails.[/red]
The statement has been terminated.


Thanks

John Fuhrman

 

[RiverGuy]

EmployeeID_PK is probably an identity column. In that case, just leave it out of the insert/select completely.

 

[sparkbyte]

When I leave it out..


(1 row(s) affected)
[red]Msg 515, Level 16, State 2, Procedure trg_AddEmployee, Line 27
Cannot insert the value NULL into column 'EmployeeID_PK', table 'MailroomTracking2.dbo.tblEmployeeNetAccts'; column does not allow nulls. INSERT fails.[/red]
The statement has been terminated.

Alter TRIGGER dbo.trg_AddEmployee On View_Employees
Instead Of Insert
As
Begin
    If Exists
    (
        Select Top 1 *
        From Inserted
    )
    Begin
        Insert Into dbo.tblEmployee
        (
--            EmployeeID_PK,
            EmployeeFN, 
            EmployeeMI, 
            EmployeeLN, 
            EmployeeDept
        )
        Select
--            NewID(),
            i.EmployeeFN, 
            i.EmployeeMI, 
            i.EmployeeLN, 
            i.EmployeeDept
        From Inserted i
;
        Insert Into dbo.tblEmployeeNetAccts
        (
--            EmployeeID_PK,
            EmployeeDomainID,
            EmployeeFIPSID
        )
        Select 
--            i.EmployeeID_PK,
            i.EmployeeDomainID,
            i.EmployeeFIPSID
        From Inserted i
    End
End

Thanks

John Fuhrman

 

[RiverGuy]

I think I see. Your EmployeeID_PK column is a UNIQUEIDENTIFIER column, not an IDENTITY column. And you need to reuse that same GUID. You could use the OUTPUT clause, but the issue I see with this is that if it is ever possible to enter the same column values more than once within the same batch, you could have issues. I would suggest generating the GUID within your application, and bringing that in from the INSERTED table. However, if that does not work, you could try the following:

Alter TRIGGER dbo.trg_AddEmployee On View_Employees
Instead Of Insert
As
Begin
    If Exists
    (
        Select Top 1 *
        From Inserted
    )
    Begin
		DECLARE @tmpEmployee TABLE (
		EmployeeID_PK UNIQUEIDENTIFIER,
		EmployeeFN VARCHAR(50), -- or whatever datatype you are using
		EmployeeMI VARCHAR(50), -- or whatever datatype you are using
		EmployeeLN VARCHAR(50), -- or whatever datatype you are using
		EmployeeDept VARCHAR(50)
		)
		
    
    
        Insert Into dbo.tblEmployee
        OUTPUT INSERTED.* INTO @tmpEmployee
        (
           EmployeeID_PK,
            EmployeeFN,
            EmployeeMI,
            EmployeeLN,
            EmployeeDept
        )
        Select
          NewID(),
            i.EmployeeFN,
            i.EmployeeMI,
            i.EmployeeLN,
            i.EmployeeDept
        From Inserted i
;
        Insert Into dbo.tblEmployeeNetAccts
        (
            EmployeeID_PK,
            EmployeeDomainID,
            EmployeeFIPSID
        )
        Select
           t.EmployeeID_PK,
            i.EmployeeDomainID,
            i.EmployeeFIPSID
        From Inserted i
        INNER JOIN @tmpEmployee t
			ON i.EmployeeFN = t.EmployeeFN
			AND i.EmployeeMI = t.EmployeeMI
			AND i.EmployeeLN = t.EmployeeLN
			AND i.EmployeeDept = t.EmployeeDept
    End
End

 

[sparkbyte]

this is getting closer.

But is not inserting the data into the 2nd table.

Alter TRIGGER dbo.trg_AddEmployee On View_Employees
Instead Of Insert
As
Begin
	If Exists
	(
		Select Top 1 *
		From Inserted
	)
	Begin
		Insert Into dbo.tblEmployee
		(
			EmployeeFN, 
			EmployeeMI, 
			EmployeeLN, 
			EmployeeDept
		)
		Select
			i.EmployeeFN, 
			i.EmployeeMI, 
			i.EmployeeLN, 
			i.EmployeeDept
		From Inserted i

		Insert Into dbo.tblEmployeeNetAccts
			(
			EmployeeID_PK,
			EmployeeDomainID,
			EmployeeFIPSID
			)
			Select 
				i.EmployeeID_PK,
				i.EmployeeDomainID,
				i.EmployeeFIPSID
		From Inserted i
			Where 
				i.EmployeeID_PK In (Select EmployeeID_PK from dbo.tblEmployee)
	End
End

Instert statement for view

insert into dbo.View_Employees
	(EmployeeFN, EmployeeMI, EmployeeLN, EmployeeDept, EmployeeDomainID, EmployeeFIPSID)
Values
	('Test','N','Dude','IT','TNDude','NoFIPS')

Results from Insert
(1 row(s) affected) --> Inserted 'Test','N','Dude','IT') into dbo.tblEmployee

(0 row(s) affected) --> Fails

(1 row(s) affected) --> Not sure what this result is from.




Thanks

John Fuhrman

 

[RiverGuy]

Here's a better/simpler version of my last reply. Sometimes the easy solutions are the hardest to see:

Alter TRIGGER dbo.trg_AddEmployee On View_Employees
Instead Of Insert
As
Begin
    If Exists
    (
        Select Top 1 *
        From Inserted
    )
    Begin
        DECLARE @tmpEmployee TABLE (
        EmployeeID_PK UNIQUEIDENTIFIER,
        EmployeeFN VARCHAR(50), -- or whatever datatype you are using
        EmployeeMI VARCHAR(50), -- or whatever datatype you are using
        EmployeeLN VARCHAR(50), -- or whatever datatype you are using
        EmployeeDept VARCHAR(50),-- or whatever datatype you are using
        EmployeeDomainID VARCHAR(50), -- or whatever datatype you are using
        EmployeeFIPSID VARCHAR(50) -- or whatever datatype you are using
    )
    INSERT INTO @tmpEmployee
    SELECT NEWID(),
    i.EmployeeFN,
    i.EmployeeMI,
    i.EmployeeLN,
    i.EmployeeDept,
    i.EmployeeDomainID,
    i.EmployeeFIPSID
    FROM INSERTED i
        
    
    
        Insert Into dbo.tblEmployee
        OUTPUT INSERTED.* INTO @tmpEmployee
        (
           EmployeeID_PK,
            EmployeeFN,
            EmployeeMI,
            EmployeeLN,
            EmployeeDept
        )
        Select
            i.EmployeeID_PK,
            i.EmployeeFN,
            i.EmployeeMI,
            i.EmployeeLN,
            i.EmployeeDept
        From @tmpEmployee i
;
        Insert Into dbo.tblEmployeeNetAccts
        (
            EmployeeID_PK,
            EmployeeDomainID,
            EmployeeFIPSID
        )
        Select
            i.EmployeeID_PK,
            i.EmployeeDomainID,
            i.EmployeeFIPSID
        From @tmpEmployee i
    End
End

 

[sparkbyte]

I like your solution. I will have to see how to incorporate you suggestions.

I did manage to get it working using an except union on the child table and setting a variable to identify the missing row.

With this working I noticed that I will have to create an instead of process for update and delete as well for this view. Is this correct?

I have a cascading PK relationship between the tables so if the user is deleted from the main table it is also removed from the child. (Tested)

Alter TRIGGER dbo.trg_AddEmployee On View_Employees
Instead Of Insert
As
Begin
	If Exists
	(
		Select Top 1 *
		From Inserted
	)
	Begin
		Insert Into dbo.tblEmployee
		(
			EmployeeFN, 
			EmployeeMI, 
			EmployeeLN, 
			EmployeeDept
		)
		Select
			i.EmployeeFN,
			i.EmployeeMI,
			i.EmployeeLN,
			i.EmployeeDept
		From Inserted i
			Where Not Exists
				(Select * From dbo.tblEmployee 
					Where 
						(i.EmployeeFN = EmployeeFN And
						i.EmployeeMI = EmployeeMI And
						i.EmployeeLN = EmployeeLN And
						i.EmployeeDept = EmployeeDept))

		If Not Exists (SELECT tblEmployee.EmployeeID_PK
						FROM tblEmployee 
						RIGHT OUTER JOIN tblEmployeeNetAccts ON 
						tblEmployee.EmployeeID_PK = tblEmployeeNetAccts.EmployeeID_PK)
			Begin
				Insert into dbo.tblEmployeeNetAccts
				(EmployeeID_PK)
				Select EmployeeID_PK from dbo.tblEmployee
			End
[red]
Declare @tt as uniqueidentifier
set @tt = (
			Select EmployeeID_PK from dbo.tblEmployee
			except
			select EmployeeID_PK from dbo.tblEmployeeNetAccts
			)[/red]
--Print @tt

		Insert Into dbo.tblEmployeeNetAccts
			(
			EmployeeID_PK,
			EmployeeDomainID,
			EmployeeFIPSID
			)
			Select 
				@tt,
				i.EmployeeDomainID,
				i.EmployeeFIPSID
		From Inserted i
	End
End

ALTER TRIGGER dbo.trg_DelEmployee On View_Employees
    INSTEAD OF DELETE
    AS
    BEGIN
        IF (SELECT COUNT(*) FROM Inserted) > 0
        BEGIN
            RAISERROR('No matching Users.',10,1)
        END
    END

ALTER TRIGGER dbo.trg_UpdateEmployee On View_Employees
    INSTEAD OF UpDate
    AS
    BEGIN
        IF (SELECT COUNT(*) FROM Inserted) > 0
        BEGIN
            RAISERROR('No matching Users.',10,1)
        END
    END

Yea... I think it works.

Now, to see if I can enter data into it from an Access ADP.

If I can UI just solved a major problem I was having with another application!!

Thanks!!!!!

Thanks

John Fuhrman