CODEWITHSUNDEEP
sqlsql-server
BeginnerCoding
BeginnerCoding

Reputation: 23

How to delete through the VIEW?

I use Microsoft SQL Server 2017 Management Studio.

I have these tables:

alter table dob
(
    PIB int primary key,
    naziv nchar (10) not null,
    broj_racuna int
)

alter table ddob
(
    PIB int primary key,
    tel nchar (10) not null,
    MB int,
    adr nchar (10)
)

PIB is foreign key to table dob

I created a view dd_all:

SELECT D.PIB, D.naziv, D.broj_racuna, DD.telefon, DD.MB, DD.adresa
FROM  dbo.dob AS D 
INNER JOIN dbo.ddo AS DD ON D.PIB = DD.PIB

I need a trigger: when I delete something from the view, that trigger needs to delete it from dob and in ddob.

I tried with this:

CREATE TRIGGER trigg_1
INSTEAD OF DELETE
AS
BEGIN
    DECLARE @pib_delete int

    SELECT @pib_delete = PIB 
    FROM dob 
    WHERE dob.PIB = @pib_delete

    DELETE FROM dobavljac_sve 
    WHERE dobavljac_sve.PIB = @pib_delete

    DELETE FROM dobavljac 
    WHERE dobavljac.PIB = @pib_delete
END

Also: 

declare @pib_delete int
select @pib_delete = PIB from dobavljac_sve where dobavljac_sve.PIB=@pib_delete

delete from dobavljac_sve where dobavljac_sve.PIB=@pib_delete
delete from dobavljac_detalji where dobavljac_detalji.PIB=@pib_delete
delete from dobavljac where dobavljac.PIB=@pib_delet

Upvotes: 0

Views: 72

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

The trigger would need to look something like this:

begin
    delete d
        from ddob d
        where d.pib in (select dd.pib from deleted dd);

    delete d
        from dob d
        where d.pib in (select dd.pib from deleted dd);

end;

Upvotes: 1

Related Questions