0

I have question about TRIGGERS.
I have (4 example) VEHICLE_TABLE with VEHICLE_TYPE column and CAR_TABLE and TRUCK_TABLE also.
I need TRIGGER for INSERT/UPDATE/DELETE who well read value of VEHICLE_TYPE column in VEHICLE_TABLE and if value of VEHICLE_TYPE is CAR he will execute
INSERT/UPDATE/DELETE on CAR_TABLE and if value of VEHICLE_TYPE column in VEHICLE_TABLE is TRUCK he will execute INSERT/UPDATE/DELETE on TRUCK_TABLE.
I am using SQL Server 2005.
Is there any idea how can I solve this problem.
Thanks in advance
David

1
Contributor
1
Reply
2
Views
9 Years
Discussion Span
Last Post by david_bronsky
0

This is the answer

CREATE TRIGGER VEHICLE_TABLE_IUD
ON VEHICLE_TABLE
AFTER INSERT,UPDATE,DELETE
AS
BEGIN
DECLARE @Vehicle varchar(50)

IF EXISTS (SELECT * FROM INSERTED)
BEGIN
SELECT @Vehicle= VEHICLE_TYPE
FROM INSERTED
IF @Vehicle='CAR'
BEGIN
INSERT INTO CAR_TABLE (fields)
SELECT fields
FROM INSERTED i
LEFT OUTER JOIN CAR_TABLE c
ON c.LinkField=i.LinkField
WHERE c.LinkField IS NULL

UPDATE c
SET c.field=i.field
.... other fields
FROM CAR_TABLE c
INNER JOIN INSERTED i
ON c.LinkField=i.LinkField
END
IF @Vehicle='TRUCK'
BEGIN
INSERT INTO TRUCK_TABLE (fields)
SELECT fields
FROM INSERTED i
LEFT OUTER JOIN TRUCK_TABLE t
ON t.LinkField=i.LinkField
WHERE t.LinkField IS NULL

UPDATE t
SET t.field=i.field
.... other fields
FROM TRUCK_TABLE t
INNER JOIN INSERTED i
ON t.LinkField=i.LinkField
END
END

IF EXISTS (SELECT * FROM DELETED) AND
NOT EXISTS (SELECT * FROM INSERTED)
BEGIN
SELECT @Vehicle= VEHICLE_TYPE
FROM DELETED
IF @Vehicle='CAR'
BEGIN
DELETE c
FROM CAR_TABLE c
INNER JOIN DELETED d
ON d.LinkTable=c.LinkTable
END
IF @Vehicle='TRUCK'
BEGIN
DELETE t
FROM TRUCK_TABLE t
INNER JOIN DELETED d
ON d.LinkTable=t.LinkTable
END
END

END
GO

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.