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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
mayhewConnect With a Mentor Commented:
Sorry for the initial oversight.

Glad it worked for you!  :)
0
 
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
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
All Courses

From novice to tech pro — start learning today.