Creating a delete trigger in SQL Sever 7

Could someone please give me an example of a simple delete trigger. Basically I have two tables in SQL Server 7 that have a relationship and I need to have a trigger that will delete the matching records from both tables. So if I delete a record from my_Table1, I need the matching record from my_Table2 deleted. I think it is something like the following but I am not sure.

CREATE TRIGGER Delete_my_Data ON my_Table1
FOR DELETE
AS
DELETE from my_table2 where my_Table1.groupid = my_Table2.groupid
GO
1817Asked:
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.

mayhewCommented:
Looks good.  The syntax, from MSDN, is:

CREATE TRIGGER [owner.]trigger_name
ON [owner.]table_name
FOR {INSERT, UPDATE, DELETE}
[WITH ENCRYPTION]
AS sql_statements


0
1817Author Commented:
I am still unclear if my example will delete the same record from my_Table2 as was deleted from my_Table1. For example, with the sample above, if I delete a record for my_Table1 with a groupid = 5, will the record in my_Table2 with a groupid = 5 get deleted?

0
1817Author Commented:
I have found the answer on my own. This response was just a cut and paste from a source I had already read. I was looking for an explanation of what it would do and a real example.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

mayhewCommented:
Ah.  Sorry, I thought you were asking for syntax.

No your example wouldn't work for that.  You would need to use the deleted table.

Whenever you do an insert or delete, SQL Server creates temp tables just like the table you inserted or deleted on.

Syntax is like


CREATE TRIGGER Delete_my_Data ON my_Table1
FOR DELETE
AS
DELETE from my_Table2, deleted where my_Table2.groupid = deleted.groupid
GO


Also, this won't work if Table2 is a child table with a foreign key constraint on Table1.  In that case you either have to remove the constraint, or use a stored proc to do the deleting from both tables.

Let me know if that works for you.
0
1817Author Commented:
That is exactly what I was looking for.

Go ahead and lock it again so you can get your points.

Thanks

0
mayhewCommented:
Sorry for the initial oversight.

Glad it worked for you!  :)
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
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.