Trigger problem

>>Is there any idea how can I solve this problem.

yes don't post your homework here

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

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

that is not the answer!!!!!!

your trigger will blow up if you update/delete/insert more that one row in a batch

a trigger fires per batch not per row!!!!

read about it in Books On Line


And why would you have a table per vehicle type anyway?
What will happen if you add 5 new vehicle types? 5 new tables? Please learn how to normalize a database!

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

David, Dennis is correct, this is an extremely poor solution. Under no circumstances should you design a trigger that will only work if you process one record. And do not fix it by adding a cursor, that will kill performance (you should never use a cursor or a loop in a trigger if there is another choice and there is almost always another choich)

To fix what you have try adding the vehicle type to the where clause instead of using an if statement.



"NOTHING is more important in a database than integrity." ESquared