SQL Server trigger on insert

Question

When I try to insert a new record into the table I created the trigger on it does not create.

This is after I activated the trigger. When I deactivate it then repeated the insert, it goes through. What am I missing in my trigger?

My trigger identifies all the new rows in a table then emails them to a recipient.

This is my trigger

USE [paldbCAT_TEST]
GO
/****** Object:  Trigger [dbo].[LPO_ON_INSERT]    Script Date: 10-11-2020 12:09:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[LPO_ON_INSERT] 
ON [dbo].[tblOrdersDocP_POApprovals]
INSTEAD OF INSERT AS

Declare
@HTMLbody nvarchar (max),
@strOrdPDocID varchar(100),
@strSelectedApproverUserName varchar(50),
@SQL nvarchar(max)

SET @SQL =''

Set @HTMLbody = '<html><head><title> System Tables</title></head><body>'
Set @HTMLbody = @HTMLbody + '<center>       <h1 style="font-family: arial, sans-serif">Purchase Requisition(s) awaiting approvals.</h1></center>'
Set @HTMLbody = @HTMLbody + '<center><table style="font-family: arial, sans-serif;border-collapse: collapse;width: 100%;" ><TH style="background-color:black; color:white;border: 1px solid #dddddd;text-align: left;padding: 8px;">Document Number</TH><TH style="background-color:black; color:white;border: 1px solid #dddddd;text-align: left;padding: 8px;">Document Approver</TH>'

declare SystemTable cursor

for
select strOrdPDocID,strSelectedApproverUserName from tblOrdersDocP_POApprovals
open SystemTable
Fetch next from SystemTable into @strOrdPDocID,@strSelectedApproverUserName


While (@@FETCH_STATUS<> -1)
Begin
Set @SQL = @SQL + '<TR><TD style=" border: 1px solid #dddddd;text-align: left;padding: 8px;">' +@strOrdPDocID + '</TD><TD style=" border: 1px solid #dddddd;text-align: left;padding: 8px;">' + @strSelectedApproverUserName + '</TD></TR>'

Fetch next from SystemTable into @strOrdPDocID,@strSelectedApproverUserName
End
Close SystemTable
DEALLOCATE SystemTable


Set @HTMLbody = @HTMLbody + @SQL + '</table></center></body></html>'




EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'CatNotificationsMain',
@recipients = '[email protected]',
@subject = 'Database Record Deleted',
@body_format='HTML',
@body = @HTMLbody,
@attach_query_result_as_file = 0;

This does not look like a trigger. It never uses inserted/deleted, and it misses CREATE TRIGGER, so we don't know if it's really trigger — sepupic
– sepupic, Commented Nov 10, 2020 at 9:03
Your trigger is "INSTEAD OF INSERT". As this name implies - it runs instead of insert, so actual insert doesn't occur. — Evk
– Evk, Commented Nov 10, 2020 at 9:14
Hey i just found a solution @Evk , the statement "INSTEAD OF INSERT" was the detriment — cocosa
– cocosa, Commented Nov 10, 2020 at 10:40
Note that you should access rows being inserted via "INSERTED" reference, not like you are doing now. — Evk
– Evk, Commented Nov 10, 2020 at 10:42

Cleptus · Accepted Answer · 2020-11-10 09:21:31Z

2

Your are creating the trigger making it replace the INSERT statement, as long as your trigger does notify the insertion, it should be run AFTER the insert has happened.

You can do further reading on the trigger types in the MS doc

ALTER trigger [dbo].[LPO_ON_INSERT] 
ON [dbo].[tblOrdersDocP_POApprovals]
AFTER INSERT AS

answered Nov 10, 2020 at 9:21

Cleptus

3,5494 gold badges31 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

cocosa · Accepted Answer · 2020-11-10 10:42:18Z

0

Hey i just found a solution @Evk , the statement "INSTEAD OF INSERT" was the detriment

answered Nov 10, 2020 at 10:42

cocosa

795 bronze badges

1 Comment

Cleptus Over a year ago

pretty much what I told you in my answer, glad you found the problem. Happy coding!

Collectives™ on Stack Overflow

SQL Server trigger on insert

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related