MS SQL Trigger

Hey All!

Ok, new to MS SQL triggers...  SO, I need to try and do the following...

1: 2 tables SYS_USER, SERV_REQUEST, and SERV_NOTE
2: When a user is deleted from SYS_USER I need all refrences in SERV_REQUEST, and SERV_NOTE to be deleted as well.
3: I want the trigger to be on the SYS_USER table.

Here is the SQL for the tables and the joins...

SELECT
  user1229049.SYS_USER.ID_USER,
  user1229049.SYS_USER.ID_DISTRICT,
  user1229049.SYS_USER.ID_BUILDING,
  user1229049.SYS_USER.[USER_NAME],
  user1229049.SYS_USER.USER_PASSWORD,
  user1229049.SYS_USER.FIRST_NAME,
  user1229049.SYS_USER.LAST_NAME,
  user1229049.SYS_USER.EMAIL,
  user1229049.SYS_USER.RIGHTS,
  user1229049.SERV_REQUEST.ID_REQUEST,
  user1229049.SERV_REQUEST.ID_DISTRICT,
  user1229049.SERV_REQUEST.ID_BUILDING,
  user1229049.SERV_REQUEST.ID_ADMIN,
  user1229049.SERV_REQUEST.ID_TECHNICIAN,
  user1229049.SERV_REQUEST.ID_USER,
  user1229049.SERV_REQUEST.ID_PRIORITY,
  user1229049.SERV_REQUEST.ID_SUB_PRIORITY,
  user1229049.SERV_REQUEST.ID_ROOM,
  user1229049.SERV_REQUEST.ID_SERVICE_TYPE,
  user1229049.SERV_REQUEST.ID_SUB_SERVICE_TYPE,
  user1229049.SERV_REQUEST.ID_START_STATUS,
  user1229049.SERV_REQUEST.ID_SERVICE_STATUS,
  user1229049.SERV_REQUEST.ID_MODIFIED_LAST_BY,
  user1229049.SERV_REQUEST.INVENTORY_TAG,
  user1229049.SERV_REQUEST.REQUEST_POSTED_DATE,
  user1229049.SERV_REQUEST.REQUEST_REQUESTED_DATE,
  user1229049.SERV_REQUEST.REQUEST_OPEN_DATE,
  user1229049.SERV_REQUEST.REQUEST_MODIFIED_DATE,
  user1229049.SERV_REQUEST.REQUEST_CLOSED_DATE,
  user1229049.SERV_REQUEST.REQUEST_SERVICE_TIME_TOTAL,
  user1229049.SERV_REQUEST.ISSUE_DESCRIPTION,
  user1229049.SERV_NOTE.ID_NOTE,
  user1229049.SERV_NOTE.ID_SERV_REQUEST,
  user1229049.SERV_NOTE.ID_USER_TYPE,
  user1229049.SERV_NOTE.POSTED_DATE,
  user1229049.SERV_NOTE.NOTE
FROM
  user1229049.SYS_USER
  INNER JOIN user1229049.SERV_REQUEST ON (user1229049.SYS_USER.ID_USER = user1229049.SERV_REQUEST.ID_USER)
  INNER JOIN user1229049.SERV_NOTE ON (user1229049.SERV_REQUEST.ID_REQUEST = user1229049.SERV_NOTE.ID_SERV_REQUEST)

Here is the start of the trigger...

CREATE TRIGGER [SYS_USER (user1229049)].[SYS_USER_trd] ON [SYS_USER (user1229049)].[]
FOR DELETE
AS
BEGIN
  /* Trigger body */
END
GO

Any help would be great!

Az
AzurdenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

OtanaCommented:
Try this:

CREATE TRIGGER [SYS_USER (user1229049)].[SYS_USER_trd] ON [SYS_USER (user1229049)].[]
FOR DELETE
AS
BEGIN
  DELETE FROM SERV_NOTE WHERE ID_SERV_REQUEST IN (SELECT ID_REQUEST FROM SERV_REQUEST WHERE USER = (SELECT ID_USER FROM deleted))
  DELETE FROM SERV_REQUEST WHERE ID_USER = (SELECT ID_USER FROM deleted)
END
GO
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jogosCommented:
Other way to do this
Make a foreign key from serv_request  to the user-table and make it 'cascading delete'.
=> when you delete the user automaticly all the refering records wil be deleted
0
doobdaveCommented:
Hi there,

may I recommend to not use triggers for this purpose?
You may or may not be aware that it's possible to have a CASCADE delete between related tables.
This is specified when creating the Foreign Key constraint:

ALTER TABLE tablename ADD CONSTRAINT foreignkeyname FOREIGN KEY(referencingcolumn) REFERENCES referencedtablename(referencedcolumnname) ON DELETE CASCADE

Best Regards,

David
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.