Skip to content

Instantly share code, notes, and snippets.

@juslintek
Created November 3, 2016 16:55
Show Gist options
  • Save juslintek/dcf5024737352a1bd62423966f99f437 to your computer and use it in GitHub Desktop.
Save juslintek/dcf5024737352a1bd62423966f99f437 to your computer and use it in GitHub Desktop.
OpenQuery in trigger prevents table from working
USE [RC2]
GO
/****** Object: Trigger [dbo].[OpencartProductsUpdate] Script Date: 2016-11-02 01:43:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Linas Jusys
-- Create date: 2016-11-02
-- Description: Atnaujina prekių duomenis OpenCart duombazėje
-- =============================================
ALTER TRIGGER [dbo].[OpencartProductsUpdate]
ON [RC2].[dbo].[Prekes]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
DECLARE @ErrorMsg VARCHAR(MAX), @ErrorNumber INT, @ErrorProc sysname, @ErrorLine INT
BEGIN TRY
DECLARE @ID INT
DECLARE @model nvarchar(20)
DECLARE @model_old nvarchar(20)
DECLARE @name nvarchar(150)
SET @ID = (SELECT PrekeID FROM inserted)
SET @model = (SELECT NomNr FROM inserted)
SET @model_old = (SELECT NomNr FROM deleted)
SET @name = (SELECT Preke FROM inserted)
INSERT INTO [Testavimui].[dbo].[DataPreview] (
[Testavimui].[dbo].[DataPreview].[columnName],
[Testavimui].[dbo].[DataPreview].[columnValue],
[Testavimui].[dbo].[DataPreview].[columnValueOld]
) VALUES (
'NomNr',
@model,
@model_old
)
DECLARE @mdxQuery varchar(4000), @sql varchar(4000)
SET @mdxQuery = 'SELECT model FROM oc_product WHERE model = ' + @model_old
SET @sql = 'UPDATE OPENQUERY (RCMYSQLOC, ''' + @mdxQuery + ''') SET model = ' + @model
BEGIN TRY
Exec(@sql)
END TRY
BEGIN CATCH
SELECT
@ErrorMsg = ERROR_MESSAGE(),
@ErrorNumber = ERROR_NUMBER(),
@ErrorProc = ERROR_PROCEDURE(),
@ErrorLine = ERROR_LINE();
/*RollBack Tran; */
INSERT INTO [Testavimui].[dbo].[ErrorLog]
(
[Testavimui].[dbo].[ErrorLog].[ErrorMsg],
[Testavimui].[dbo].[ErrorLog].[ErrorNumber],
[Testavimui].[dbo].[ErrorLog].[ErrorProc],
[Testavimui].[dbo].[ErrorLog].[ErrorLine]
)
VALUES
(
@ErrorMsg,
@ErrorNumber,
@ErrorProc,
@ErrorLine
)
END CATCH
/*Execute ('UPDATE oc_product SET model = "'+@model+'" WHERE model = "'+@model_old+'"')at RCMYSQLOC;*/
/*BEGIN TRAN
IF UPDATE(NomNr)
/*Execute ('UPDATE oc_product SET model = "'+@model+'" WHERE model = "'+@model_old+'"')at RCMYSQLOC;
UPDATE oq SET oq.model = @model
FROM openquery(RCMYSQLOC, 'SELECT model FROM oc_product') oq
INNER JOIN [RC2].[dbo].[Prekes] pr ON pr.PrekeID = @ID
WHERE oq.model = pr.NomNr
UPDATE oq SET oq.name = @name
FROM openquery(RCMYSQLOC, 'SELECT name FROM oc_product_description') oq
INNER JOIN [RC2].[dbo].[Prekes] pr ON pr.PrekeID = @ID
WHERE oq.model = pr.NomNr
UPDATE oq SET oq.model = @model
FROM openquery(RCMYSQLOC, 'SELECT model FROM oc_product') oq
INNER JOIN [RC2].[dbo].[Prekes] pr ON pr.PrekeID = @ID
WHERE oq.model = pr.NomNr
UPDATE oq SET oq.model = @model
FROM openquery(RCMYSQLOC, 'SELECT model FROM oc_product') oq
INNER JOIN [RC2].[dbo].[Prekes] pr ON pr.PrekeID = @ID
WHERE oq.model = pr.NomNr
UPDATE oq SET oq.model = @model
FROM openquery(RCMYSQLOC, 'SELECT model FROM oc_product') oq
INNER JOIN [RC2].[dbo].[Prekes] pr ON pr.PrekeID = @ID
WHERE oq.model = pr.NomNr */
COMMIT TRAN */
END TRY
BEGIN CATCH
SELECT
@ErrorMsg = ERROR_MESSAGE(),
@ErrorNumber = ERROR_NUMBER(),
@ErrorProc = ERROR_PROCEDURE(),
@ErrorLine = ERROR_LINE();
/*RollBack Tran; */
INSERT INTO [Testavimui].[dbo].[ErrorLog]
(
[Testavimui].[dbo].[ErrorLog].[ErrorMsg],
[Testavimui].[dbo].[ErrorLog].[ErrorNumber],
[Testavimui].[dbo].[ErrorLog].[ErrorProc],
[Testavimui].[dbo].[ErrorLog].[ErrorLine]
)
VALUES
(
@ErrorMsg,
@ErrorNumber,
@ErrorProc,
@ErrorLine
)
END CATCH
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment