Home SQL How to restrict parent row from updates once child added?
Reply: 2

SQL How to restrict parent row from updates once child added?

Ivan Idea
1#
Ivan Idea Published in 2017-11-14 19:49:37Z

I use foreign keys to prevent rows from being deleted in some SQL tables once a child uses the key. However, now I want to be able to restrict any column of the parent table from updates once the child uses the key.

For example if the parent table is [ID],[First Name],[Last Name]

And the child table is [ListID],[Emp ID]

The foreign key prevents me from deleting or updating [ID] in the parent table however, it doesn't stop me from modifying [First Name]. Is there a way to set up foreign keys to prevent this?

Thanks.

Ajith
2#
Ajith Reply to 2017-11-15 07:07:52Z

you could use instead of trigger.

CREATE TABLE [dbo].[ParentTable]
(
ID INT, FirstName VARCHAR(50), LastName VARCHAR(50)
)
go

CREATE table ChildTable(EmpID INT)

go

CREATE TRIGGER [dbo].[ParentTable_InsteadOfUPDATE]
       ON [dbo].[ParentTable]
INSTEAD OF UPDATE
AS
BEGIN


       DECLARE @ID INT, @FirstName VARCHAR(50), @LastName VARCHAR(50)

       SELECT @ID = INSERTED.ID,
              @FirstName = INSERTED.FirstName,
              @LastName = INSERTED.LastName       
       FROM INSERTED

    IF EXISTS(select top 1 1 from DBO.ChildTable Where EmpID=@ID )
    BEGIN

       IF UPDATE([ID]) OR UPDATE([FirstName]) OR UPDATE([LastName])
       BEGIN
              RAISERROR('These fields cannot be updated.', 16 ,1)
              ROLLBACK
       END    
    END
    ELSE
    BEGIN
        Update [ParentTable] SET FirstName=@FirstName,LastName=@LastName Where ID=@ID;
    END   
END
go
gotqn
3#
gotqn Reply to 2017-11-15 07:11:00Z

Try this:

CREATE TRIGGER trg_name ON parent_table INSTEAD OF UPDATE
AS
BEGIN

    UPDATE parent_table
    SET [FirstName] = I.[firstname]
       ,[LastName] = I.[lastname]
     FROM parent_table A
     INNER JOIN inserted B
        ON A.[ID] = B.[ID]
     LEFT JOIN child_table CT
        ON A.[ID] = CT.[ListID] -- or [EmpID] - am not sure about the relation
     WHERE CT.[LsitID] IS NULL  -- where the element from parent_table does not have element in the child table

END

Also, always try to handle the logic in triggers in batches in order not to hurt the performance.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.307765 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO