当列达到特定值时，如何添加触发器以删除行 [英] How do I add a trigger to delete a row when a column reaches a certain value

问题描述

我是sql server的新手。我有问题，当数量列的值达到零时添加触发器来删除记录。我正在使用netbeans和sql server。



什么我试过了：



i是新的，所以我还没有尝试过任何东西。

解决方案

< blockquote>你好，



这是当行数达到12时从表中删除行的解决方案。这个演示示例我们在历史记录中维护用户密码（每个用户的最近12个密码表。

  CREATE   TRIGGER  [dbo]。[trgChangePassword]  ON  [dbo]。[UserPassword] 
  FOR   INSERT  
  AS  
  DECLARE   @ userid   INT  
  DECLARE   @ totalrec   INT  
  SELECT   @ userid  = UserId  FROM 已插入
  SELECT  @ totalrec = COUNT（*） FROM  UserPassword 其中 UserId = @ userid; 
  IF   @ totalrec > =  12  
  BEGIN  
  DELETE   FROM  UserPassword  WHERE  Id  IN （ SELECT   TOP   1  Id  FROM  UserPassword  WHERE  UserId = @ userid  ORDER   BY  id  ASC ）
 结束 

i am new to sql server . i am having problem to add a trigger to delete a record when the value of quantity column reaches zero .i am working with netbeans and sql server.

What I have tried:

i am new so i haven't tried anything yet.

解决方案

Hello,

Here is the solution to remove the rows from table when row count is reached to 12. this demo sample where we are maintaining users password in history (most recent 12 passwords) table for each users.

CREATE TRIGGER [dbo].[trgChangePassword] ON [dbo].[UserPassword] 
FOR INSERT
AS
	DECLARE @userid INT
	DECLARE @totalrec INT
	SELECT @userid = UserId FROM inserted
	SELECT @totalrec=COUNT(*) FROM UserPassword where UserId=@userid;
	IF @totalrec >= 12
	BEGIN 
		DELETE FROM UserPassword WHERE Id IN (SELECT TOP 1 Id FROM UserPassword WHERE UserId=@userid ORDER BY id ASC)
	End

这篇关于当列达到特定值时，如何添加触发器以删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！