Question

MS SQL Server update a new row after inserting before final action using a query or trigger

  • 15 December 2022
  • 3 replies
  • 284 views

I’m having a difficult time figuring something out with Zapier. Here’s my flow and what I’m trying to accomplish. This is all contained in a single Zap.

 

  1. I have a Zapier catch WebHook that captures data from my vendor’s website – this works
  2. That data is processed as a new row in a table in our SQL Server DB –  this portion works without my trigger (see item 3 below)
  3. I need to update that new row’s data in our SQL Server with additional data before passing the new row’s data to WooSender via Zapier. – Not working
  4. The data is sent to WooSender for their processing. - works

 

Here is what I have tried but to no avail. I created an after-insert trigger in our SQL server that gathers and updates this new record created from the Zapier WebHook contents adding the rest of the data I need to pass to WooSender. (i.e. time zone based on a zip code, WooSender campaign information, etc. The trigger is causing the action in Zapier to fail.


This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

3 replies

Userlevel 6
Badge +8

Hey Alvin,

What’s the error message you’re getting? Is it something like “The target table '<table name>' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.”?

If so, I can tell you that at least one user has been able to work around that limitation by having the Zapier action issue the primary key.

Let me know about the error message and whether or not that workaround works for you…

Thanks!

Yes that is the error: (pyodbc.ProgrammingError) ('42000', "[42000] [FreeTDS][SQL Server]The target table 'tblLeadRawData' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause. (334) (SQLExecDirectW)") [SQL: 'INSERT INTO [tblLeadRawData] ([HomePhone], [AltPhone], [Email], [FirstName], [LastName], [Address], [City], [State], [ZipCode], [VendorCode], [Campaign], [Note], [PipeLine], [Stage], [IPAddress]) OUTPUT inserted.[LeadReferenceID] VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'] [parameters: ('3123583659', '3123583659', 'xueqing40@gmail.com', 'Qing', 'Xue-Hope-Test', '5725 California Circle', 'Banning', 'CA', '92220', 'DMS', 'Upside Legal', '0', 'Upstart Pipeline', 'Lead', '42.111.145.246')] (Background on this error at: http://sqlalche.me/e/f405)

Do I put the output in the SQL trigger and where, top, bottom, middle? If the Zapier action, how do I do that? Here is the SQL trigger:

DECLARE @TMZ as nvarchar(4)
    DECLARE @ZipCode as varchar(50)
    DECLARE @LeadReferenceID as int

SET NOCOUNT ON;

    SELECT @ZipCode=INSERTED.ZipCode, @LeadReferenceID=INSERTED.LeadReferenceID from INSERTED
            
    SELECT @TMZ=[T/Z] FROM tblZipCodeTMZ WHERE 1=1 and ZIP=@ZipCode
    
    UPDATE tblLeadRawData 
    SET TimeZone=@TMZ, 
    LeadCreated=convert(nvarchar(12), getdate(), 110), 
    Stage='Lead', 
    PipeLine='Upstart Pipeline',
    Campaign='(U10) Upside Lead Gen Debt - ' + @TMZ
    WHERE 1=1 
    and LeadReferenceID = @LeadReferenceID