February 3, 2012 at 2:23 am
Hi , I am trying to create a trigger that will insert data into explore_hist from explore when data is updated in explore.
the query is below..please correct the query below...its shows syntax error
CREATE TRIGGER Employee_Trigger
BEFORE UPDATE ON EXPLORE
FOR EACH ROW
BEGIN;
INSERT into EXPLORE_HIST
set HNO = 1
HNAME = 'try';
END;
February 3, 2012 at 5:17 am
HI Stewart ,
I wrote the query as u suggested ,but its still throwing a syntax error before the select keyword.Please correct me in this
CREATE TRIGGER Employee_Trigger
ON Explore
AFTER UPDATE
AS
BEGIN
TRY
INSERT Explore_Hist (HNO,HNAME) SELECT NO,NAME FROM INSERTED;
COMMIT TRANSACTION
END TRY
BEGIN CATCH
RAISERROR('Error inserting into Explore_Hist table; Investigate and resubmit',16,1)
ROLLBACK
END CATCH
GO
February 3, 2012 at 5:32 am
i wrapped it with a BEGIN / END to mark the limits of the trigger, and it seems to work fine.
based on something in my snippets, i added some extra information you can get from some of the internal variables:
CREATE TRIGGER Employee_Trigger
ON Explore
AFTER UPDATE
AS
BEGIN --TRIGGER
BEGIN TRY
INSERT Explore_Hist (HNO,HNAME) SELECT NO,NAME FROM INSERTED;
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @ErrorSeverity INT, @ErrorNumber INT,
@ErrorMessage NVARCHAR(4000), @ErrorState INT
SET @ErrorSeverity = ERROR_SEVERITY()
SET @ErrorNumber = ERROR_NUMBER()
SET @ErrorMessage = ERROR_MESSAGE()
SET @ErrorState = ERROR_STATE()
IF @ErrorState = 0
SET @ErrorState = 1
RAISERROR('Error inserting into Explore_Hist table; Investigate and resubmit:%d',@ErrorState,@ErrorNumber)
ROLLBACK TRANSACTION
END CATCH
END --TRIGGER
GO
Lowell
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply