Trigger problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trigger problem

Author

Topic

david_bronsky
Starting Member

2 Posts

Posted - 2008-02-09 : 20:21:37

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

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-02-09 : 21:49:54

You may need to provide some more information...are the related tables joined by a key of some kind? part of the VIN (usually the 1st 8 digits and the 10th digit) to designate your groups?

Do you intend on keeping both tables with identical record counts? or is the vehicle type table a smaller subset...

what if the vehicle table has a VIN or code that does not appear in the vehicle_type table?

Poor planning on your part does not constitute an emergency on my part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-10 : 00:37:06

It will be like this:-

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

if you want more specific solution please post your table structures and also relationships b/w them.

david_bronsky
Starting Member

2 Posts

Posted - 2008-02-11 : 12:01:26

Thanks,visakh16

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources