如何通过单击按钮删除？ [英] How do I delete with one button click?

问题描述

我有一个带有两个删除语句的存储过程
来自租户的
delete *其中tenant-id = @ tenant -id

delete * from lease where tenant-id = @ tenant-id



问题是我必须在我的应用程序中单击两次删除按钮才能执行存储过程。



我尝试过的事情：



删除租户，租赁租户租户Tenant内部加入Tenant.tenant-id =租赁.tenant-id



但我在'，'

I have a stored procedure with two delete statements
delete * from Tenant where tenant-id =@tenant -id
delete * from lease where tenant-id =@tenant-id

issue is i have to click delete button twice in my application to execute the stored procedure.

What I have tried:

delete Tenant,lease from Tenant inner join on Lease where Tenant.tenant-id=Lease.tenant-id

but i get error incorrect syntax before ','

推荐答案

你可以'之前得到错误的语法错误使用DELETE从两个表中删除 - 你可以在MySql中，但不能在SQL Server中 - 所以当你尝试第二个代码时，你会遇到语法错误。



单击两次按钮问题不太可能是SQL问题 - 您需要查看C＃代码并确切了解它执行存储过程的操作。



和BTW：不要在列名中使用连字符：while你可以，这是一个坏主意，因为当你尝试使用它时，连字符将终止列名，除非你每次使用它时用方括号括起它，甚至不想使用带有连字符的参数名称：

You can't use DELETE to remove from two tables - you can in MySql, but not in SQL Server - so when you tried the second code, you got a syntax error.

The "button click twice" problem is unlikely to be an SQL problem - you need to look at your C# code and see exactly what it is doing to execute the stored procedure.

ANd BTW: don't use hyphens in column names: while you can, it's a bad idea as the hyphen will terminate the column name when you try to use it unless you surround it with square brackets every time you use it, and don't even think of using parameters names with hyphens in:

DELETE * FROM Tenant WHERE [Tenant-ID]=@TenantID

乍一看，您的租户和租约似乎已连接父母与孩子分别。首先想到的是我永远不会删除这些东西。您将需要在某些时候回顾它们。去年取消了多少租约？有多少人拖欠租约？程序用户多少次搞砸了并且在他们不应该的时候输入租约？否......将租约标记为已终止，已过期，无效，等等。不要删除它。特别是在与客户合作时，他们可能会在某个时候打电话说嘿，我上个月付了两次而且我要退款。



但是！如果您确实要删除它，那么您的存储过程可以在单独的行中删除多个。或者，您可以使用CASCADE Delete和CASCADE更新在数据库中设置关系。 Sql Server将负责清理相关记录，只删除父级。级联是FOREIGN KEY的变种，默认是无动作，但你可以将它改为CASCADE。



一般语法

ALTER表具有CHECK ADD CONSTRAINT的表ChildTable [FK_ChildTable_ParentTable] FOREIGN KEY（ParentId）REFERENCES ParentTable（ParentId）ON UPDATE CASCADE ON DELETE CASCADE



您的特定情况

ALTER TABLE Lease WITH CHECK ADD CONSTRAINT [FK_Lease_Tennant] FOREIGN KEY（tennant_id）REFERENCES Tennant（tennant_id）ON UPDATE CASCADE ON DELETE CASCADE



你的SP只需要删除Tennant。您可以设置类似的关系以处理其他表，付款等。我必须重申最初的想法，这些不是你应该删除的东西的类型。我保证，你*会在某个时候再次需要它们。此外，回到级联关系，关于这样做是否会降低性能存在很多争论。如果您正在使用包含数十万条记录的表格，那么应该小心。



[更新]我差点忘了提及。如果您设置了一个将租户与租赁相关联的外键，并且租约与租户相关*没有*级联设置，则您无法先删除父记录。您必须删除租约，然后才能删除租户。这是因为租约中的约束要求匹配租户出现。



HTH，

Mike

At first glance it appears that your tennant and your lease are connected parent to child respectively. First thought that I have is never ever ever delete these things. You will want to refer back to them at some point. How many leases were cancelled last year? How many people defaulted on their lease? How many times did the program user mess up and enter a lease when they weren't supposed to? No... mark the lease as terminated, expired, invalid, whatever. Don't delete it. Especially when working with customers who might at some point call in and say "Hey, I paid twice last month and I want a refund".

However! If you really want to delete it, then your stored procedure can delete more than one in separate lines. OR you can set up a relation in your database with CASCADE Delete and CASCADE update. Sql Server will take care of cleaning up the related records, you delete only the parent. The cascade is a variant of the FOREIGN KEY, the default is no action but you can change it to CASCADE.

General syntax
ALTER TABLE ChildTable WITH CHECK ADD CONSTRAINT [FK_ChildTable_ParentTable] FOREIGN KEY(ParentId) REFERENCES ParentTable(ParentId) ON UPDATE CASCADE ON DELETE CASCADE

Your specific case
ALTER TABLE Lease WITH CHECK ADD CONSTRAINT [FK_Lease_Tennant] FOREIGN KEY(tennant_id) REFERENCES Tennant(tennant_id) ON UPDATE CASCADE ON DELETE CASCADE

Your SP would only have to delete the Tennant. You can set up similar relations to work on other tables, payments, and so forth. I have to restate the original thought, these are not the types of things you should delete. You *will* need them again at some point, I promise. Also, back on the cascade relations, there has been much debate over whether or not doing this degrades performance. If you're working with tables with hundreds of thousands of records then care should be taken.

[UPDATE] I almost forgot to mention. If you have a foreign key set up that relates tenant to lease and the lease relates into tenant *without* the cascade set up then you cannot delete the parent record first. You have to delete the lease, then you can delete the tenant. This is because the constraint in the lease requires the matching tenant to be present.

HTH,
Mike

这篇关于如何通过单击按钮删除？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！