Trigger Code - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Trigger Code

Author

Topic

Steve2106
Posting Yak Master

183 Posts

Posted - 2015-02-17 : 10:06:06

Hi There,

In need of your help again if you don't mind.
I need some code for a trigger for when a new record is added to a table.

I have 2 tables, Table1 & Table2
When a new record is added to table1 with field1 = 4, I need to delete a record in Table2 where the description field in both tables match.

Can that be done?

Your help is appreciated.

Best Regards,

Always Learning.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-17 : 11:38:01

create trigger mytrigger on table1 for after insert
as
if @@rowcount = 0 return
delete from table2
where inserted.desc = table2.desc

Steve2106
Posting Yak Master

183 Posts

Posted - 2015-02-17 : 11:52:49

Hi There,

Thanks for the reply.
That looks easy enough but how do I check that field1 = 4
As records are being added all the time I only want to delete a record if field1 = 4

Thanks again for your help.

Best Regards,

Always Learning.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-17 : 12:19:45

add

AND inserted.field1 = 4

to the WHERE clause

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-02-17 : 14:52:17

[code]
CREATE TRIGGER table1__trg_insert
ON dbo.table1
AFTER INSERT
AS
SET NOCOUNT ON
IF EXISTS(SELECT 1 FROM inserted)
BEGIN
DELETE FROM t2
FROM dbo.table2 t2
INNER JOIN (
SELECT DISTINCT description
FROM inserted
WHERE
field1 = 4
) AS i ON i.description = t2.description
END

[/code]

Steve2106
Posting Yak Master

183 Posts

Posted - 2015-02-18 : 05:03:05

Hi There,

Thanks for your help.

I really appreciate your time.

Best Regards,

Always Learning.

Steve2106
Posting Yak Master

183 Posts

Posted - 2015-02-18 : 05:03:15

Hi There,

Thanks for your help.

I really appreciate your time.

Best Regards,

Always Learning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources