Home SQL server- delete row from table using Trigger
Reply: 2

SQL server- delete row from table using Trigger

Gavriel
1#
Gavriel Published in 2018-01-12 10:51:04Z

I have a problem with SQL server, I need to create a trigger that works that way:

Every time when I insert information to tblNotInterested (Inside the table I have two columns "email1" and "email2"). The trigger needs to check if "email1" and "email2" already exists in a different table named tblListOf. If they exist I need to delete the row in which they were found.

Ruben1990
2#
Ruben1990 Reply to 2018-01-12 11:01:55Z

Maybe you can do something like this:

DELETE FROM tblListOf 
WHERE EXISTS (SELECT * FROM tblNotInterested
WHERE tblNotInterested.email1 = tblListOf.email1 and tblNotInterested.email2 = tblListOf.email2)
SqlOnly
3#
SqlOnly Reply to 2018-01-12 17:45:58Z

inserted and deleted tables can be quite useful, but they are definitely buyer beware because ...

  • they don't always lend themselves to easy documentation
  • they are invisible to client applications
  • they may have performance, or scalability issues, due to expensive locks

That being said, I believe the below code should work for this case. One final caution/question may be around nullable columns in your two tables. If either email1 or email2 is nullable on any table, I would consider re-evaluating this code.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER dbo.trig_i_tblNotInterested_MatchingEmail1AndEmail2
   ON  dbo.tblNotInterested
   AFTER INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    DELETE L
        FROM dbo.tblListOf L
        INNER JOIN inserted ins
            ON L.email1 = ins.email1
            AND L.email2 = ins.email2
END
GO
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.300723 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO