delete trigger

Hi how can i use delete trigger?

For example i've a two table like emp and emp_personal and now what i want to do is

i want to delete one row from emp table so how is it possible to delete automatically that emp's details from second table(emp_personal) ?

Ex:

emp emp_personal

emp_id emp_name emp_basic emp_id emp_address

101 Nagu 32,000 101 India

102 Vijay 35,000 102 South Africa

103 Ritesh 30,000 103 U.S

I want to delete employee who hav emp_id of 102 from emp table , so how can i delete automatically that employee details from second table i.e. emp_personal ?

Is it possible with triggers?

Thanx - Nagu

hi Nagu,

you can implement a trigger on DELETE action but you can even implement a foreign key constraint with the desired action, that's to say ON UPDATE CASCADE and ON DELETE CASCADE to maintain referential integrity as

SET NOCOUNT ON;

USE tempdb;

GO

CREATE TABLE dbo.Emp (

emp_id int NOT NULL PRIMARY KEY,

emp_name varchar(10) NOT NULL,

emp_basic decimal (18,4) NULL

);

CREATE TABLE dbo.Emp_Personal (

emp_id int NOT NULL PRIMARY KEY,

emp_address varchar(15) NOT NULL

);

GO

INSERT INTO dbo.Emp VALUES ( 101 , 'Nagu', 32 );

INSERT INTO dbo.Emp VALUES ( 102 , 'Vijay', 35 );

INSERT INTO dbo.Emp VALUES ( 103 , 'Rithesh', 30 );

INSERT INTO dbo.Emp_Personal VALUES ( 101, 'India' );

INSERT INTO dbo.Emp_Personal VALUES ( 102, 'South Africa' );

INSERT INTO dbo.Emp_Personal VALUES ( 103, 'US' );

GO

SELECT e.emp_id, e.emp_name, e.emp_basic,

p.emp_address

FROM dbo.Emp e

LEFT JOIN dbo.Emp_Personal p ON e.emp_id = p.emp_id

ORDER BY e.emp_id;

GO

CREATE TRIGGER tr_D_Emp ON dbo.Emp

FOR DELETE

AS BEGIN

IF @.@.ROWCOUNT = 0 RETURN;

DELETE FROM dbo.Emp_Personal

WHERE emp_id IN (SELECT emp_id FROM deleted);

END;

GO

DELETE FROM dbo.Emp WHERE emp_id > 101;

SELECT e.emp_id, e.emp_name, e.emp_basic,

p.emp_address

FROM dbo.Emp e

LEFT JOIN dbo.Emp_Personal p ON e.emp_id = p.emp_id

ORDER BY e.emp_id;

GO

DROP TRIGGER tr_D_Emp ;

GO

ALTER TABLE dbo.Emp_Personal

ADD CONSTRAINT fk_Emp$have$Emp_Personal

FOREIGN KEY (emp_id)

REFERENCES dbo.Emp (emp_id)

ON UPDATE CASCADE

ON DELETE CASCADE;

GO

INSERT INTO dbo.Emp VALUES ( 102 , 'Vijay', 35 );

INSERT INTO dbo.Emp VALUES ( 103 , 'Rithesh', 30 );

INSERT INTO dbo.Emp_Personal VALUES ( 102, 'South Africa' );

INSERT INTO dbo.Emp_Personal VALUES ( 103, 'US' );

SELECT e.emp_id, e.emp_name, e.emp_basic,

p.emp_address

FROM dbo.Emp e

LEFT JOIN dbo.Emp_Personal p ON e.emp_id = p.emp_id

ORDER BY e.emp_id;

DELETE FROM dbo.Emp WHERE emp_id < 103;

SELECT e.emp_id, e.emp_name, e.emp_basic,

p.emp_address

FROM dbo.Emp e

LEFT JOIN dbo.Emp_Personal p ON e.emp_id = p.emp_id

ORDER BY e.emp_id;

GO

DROP TABLE dbo.Emp_Personal, dbo.Emp;

--<--

emp_id emp_name emp_basic emp_address

-- -

101 Nagu 32.0000 India

102 Vijay 35.0000 South Africa

103 Rithesh 30.0000 US

emp_id emp_name emp_basic emp_address

-- -

101 Nagu 32.0000 India

emp_id emp_name emp_basic emp_address

-- -

101 Nagu 32.0000 India

102 Vijay 35.0000 South Africa

103 Rithesh 30.0000 US

emp_id emp_name emp_basic emp_address

-- -

103 Rithesh 30.0000 US

regards

|||

Thank you Andrea

Nagu