Posted by Masterx81 on 07/06/07 16:04

Hi to all...
I've a little trouble with a t-sql db...
I've 2 tables int he db.
On the child table i've a trigger that do some controls and some
calcs
that are saved on a 3 table.
When i've created the db, i've added the reference from the child to
the parent, with the option ON DELETE CASCADE.
The problem is that when i remove the child (via the reference) rows,
i must access some data that are on the parent table.
What i can do to access with the trigger to the parent row data while
removing the child rows?

Follow the SQL code that give me the problem:
----------------------------------------------------


CREATE DATABASE Attrezzature


use attrezzature


CREATE TABLE Movimenti (id int NOT NULL IDENTITY (1, 1) PRIMARY KEY
("id"), cod_movimento int)


CREATE TABLE AttrezzatureMovimento (id int NOT NULL IDENTITY (1, 1)
PRIMARY KEY ("id"), id_movimento int REFERENCES movimenti(id) ON
DELETE CASCADE)


INSERT INTO movimenti (cod_movimento) VALUES (333)


INSERT INTO AttrezzatureMovimento (id_movimento) VALUES (1)


CREATE TRIGGER TR_DEL_QTAMovimento
ON AttrezzatureMovimento
FOR DELETE
AS


DECLARE @test int
SET @test = (select cod_movimento from movimenti where id = (select
id_movimento from Deleted))


if (@test IS NULL)
BEGIN
RAISERROR ('Impossibile predere i dati dalla tabella padre!',
18,1)
ROLLBACK TRANSACTION
RETURN
END


delete from movimenti where id = 1


----------------------------------------------------


What i can do???


Very thanks!

• Next in forum: Help writing an If else statement
• Prev in forum: ISQL is truncating contents of text field sent to text file.
• Thread view: Trigger ed on delete...

[Reply to this message]