Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Creating a delete trigger in SQL Sever 7

Posted on 1999-07-01
6
Medium Priority
?
491 Views
Last Modified: 2010-03-19
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
0
Comment
Question by:1817
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 5

Expert Comment

by:mayhew
ID: 1096076
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
 

Author Comment

by:1817
ID: 1096077
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
 

Author Comment

by:1817
ID: 1096078
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 5

Expert Comment

by:mayhew
ID: 1096079
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
 

Author Comment

by:1817
ID: 1096080
That is exactly what I was looking for.

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

Thanks

0
 
LVL 5

Accepted Solution

by:
mayhew earned 400 total points
ID: 1096081
Sorry for the initial oversight.

Glad it worked for you!  :)
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question