Created
November 3, 2016 16:55
-
-
Save juslintek/dcf5024737352a1bd62423966f99f437 to your computer and use it in GitHub Desktop.
OpenQuery in trigger prevents table from working
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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