February 13, 2017 at 9:38 am
I wanted to Validate the code againt the look up table before i insert into a Base table.
I have two table Table A ( Look up) Tbale B ( where inserts happen)
So when ever i try to insert a code in Table B , i want to validate if that code exists in tableA only then insert into
TableB. Using Try catch block.
Something like below:
Create TRIGGER Code ON TABLEB
INSTEAD OF INSERT AS
BEGIN
DECLARE @Code NVarchar(250)
SELECT @Code = Code from TABLEA AS [M]
INNER JOIN INSERTED I ON M.Code = I.CODE
IF (@code is NULL )
BEGIN
RAISERROR (N'The provided Code does not exist in TableA',16, 1)
RETURN
END
INSERT INTO TABLEB (Code)
SELECT @code
FROM inserted
END
Still need to update with try catch block. Right now i have problem , even if i nsert correct code , insert does not happen.
February 13, 2017 at 9:49 am
First, that's going to fail if you insert more than one row at a time. Second, have you considered a foreign key constraint instead of a trigger?
John
February 13, 2017 at 9:56 am
John Mitchell-245523 - Monday, February 13, 2017 9:49 AMFirst, that's going to fail if you insert more than one row at a time. Second, have you considered a foreign key constraint instead of a trigger?John
The insert happens through access on the user side. So we have several codes to validate for the same tableB insertion. So, tables are already created ,when ever they insert incorrect code we need to show them the code is not valid to enter into a table.
Also how we can validate several codes in the same trigger?
February 13, 2017 at 10:26 am
There is also some weirdness with your code. Your:SELECT @code
FROM inserted
line is strange. You don't need a FROM when you are just selecting the variable.
But I agree, a Foreign Key Constraint is a much safer way to go and then use a try-catch block in your stored procedure that does the insert.
And as John pointed out, the code:SELECT @Code = Code from TABLEA AS [M]
INNER JOIN INSERTED I ON M.Code = I.CODE
will fail if inserted contains more than 1 row AND more than 1 code matches.
A FK would be much faster for your inserts as well and you could get stuck in an infinite loop depending on your settings.
BUT if the code you provided is what you want, change this line:INSERT INTO TABLEB (Code)
toINSERT INTO TABLEB (@Code)
But to me, this looks like a recipe for disaster.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 13, 2017 at 10:37 am
Yet another vote from me on implementing this as a FK.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 13, 2017 at 12:43 pm
bmg002 - Monday, February 13, 2017 10:26 AMThere is also some weirdness with your code. Your:SELECT @code
FROM inserted
line is strange. You don't need a FROM when you are just selecting the variable.But I agree, a Foreign Key Constraint is a much safer way to go and then use a try-catch block in your stored procedure that does the insert.
And as John pointed out, the code:
SELECT @Code = Code from TABLEA AS [M]
INNER JOIN INSERTED I ON M.Code = I.CODE
will fail if inserted contains more than 1 row AND more than 1 code matches.A FK would be much faster for your inserts as well and you could get stuck in an infinite loop depending on your settings.
BUT if the code you provided is what you want, change this line:
INSERT INTO TABLEB (Code)
toINSERT INTO TABLEB (@Code)But to me, this looks like a recipe for disaster.
ALTER TRIGGER Trg_Code ON TABLEB
INSTEAD OF INSERT AS
BEGIN
DECLARE @CompetitorCode nVarchar(250)
SELECT @CompetitorCode = M.Code From TABLEA AS [M]
INNER JOIN INSERTED I ON M.Code = I.CompetitorType_Code
BEGIN TRY
IF (@Code is Not NULL)
INSERT INTO TABLEB(CompetitorType_Code)
Select @CompetitorCode
END TRY
BEGIN CATCH
IF (@CompetitorCode is NULL)
BEGIN
RAISERROR (N'The provided CompetitorTypeCode does not exist in TableB',16, 1);
ROLLBACK TRANSACTION;
RETURN
END
END CATCH
END
I added try catch block and it does not do what it has to do. Can you please check the code?
February 13, 2017 at 12:49 pm
komal145 - Monday, February 13, 2017 12:43 PMbmg002 - Monday, February 13, 2017 10:26 AMThere is also some weirdness with your code. Your:SELECT @code
FROM inserted
line is strange. You don't need a FROM when you are just selecting the variable.But I agree, a Foreign Key Constraint is a much safer way to go and then use a try-catch block in your stored procedure that does the insert.
And as John pointed out, the code:
SELECT @Code = Code from TABLEA AS [M]
INNER JOIN INSERTED I ON M.Code = I.CODE
will fail if inserted contains more than 1 row AND more than 1 code matches.A FK would be much faster for your inserts as well and you could get stuck in an infinite loop depending on your settings.
BUT if the code you provided is what you want, change this line:
INSERT INTO TABLEB (Code)
toINSERT INTO TABLEB (@Code)But to me, this looks like a recipe for disaster.
ALTER TRIGGER Trg_Code ON TABLEB
INSTEAD OF INSERT AS
BEGINDECLARE @CompetitorCode nVarchar(250)
SELECT @CompetitorCode = M.Code From TABLEA AS [M]
INNER JOIN INSERTED I ON M.Code = I.CompetitorType_Code
BEGIN TRY
IF (@Code is Not NULL)
INSERT INTO TABLEB(CompetitorType_Code)
Select @CompetitorCode
END TRY
BEGIN CATCH
IF (@CompetitorCode is NULL)BEGIN
RAISERROR (N'The provided CompetitorTypeCode does not exist in TableB',16, 1);
ROLLBACK TRANSACTION;
RETURN
END
END CATCH
ENDI added try catch block and it does not do what it has to do. Can you please check the code?
I noticed my previous comment was wrong about the INSERT INTO TABLEB(@code).
but your code line:IF (@Code is Not NULL)
Is the problem. It should be @CompetitorCode.
But I am still concerned about infinite loops. Do you have "Allow Triggers to Fire Others" set to true? If so, you might get hit by infinite trigger loops.
EDIT - just was re-reading your code and you have a ROLLBACK TRANSACTION without a BEGIN TRANSACTION? That'll be troublesome too.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 13, 2017 at 12:54 pm
bmg002 - Monday, February 13, 2017 12:49 PMkomal145 - Monday, February 13, 2017 12:43 PMbmg002 - Monday, February 13, 2017 10:26 AMThere is also some weirdness with your code. Your:SELECT @code
FROM inserted
line is strange. You don't need a FROM when you are just selecting the variable.But I agree, a Foreign Key Constraint is a much safer way to go and then use a try-catch block in your stored procedure that does the insert.
And as John pointed out, the code:
SELECT @Code = Code from TABLEA AS [M]
INNER JOIN INSERTED I ON M.Code = I.CODE
will fail if inserted contains more than 1 row AND more than 1 code matches.A FK would be much faster for your inserts as well and you could get stuck in an infinite loop depending on your settings.
BUT if the code you provided is what you want, change this line:
INSERT INTO TABLEB (Code)
toINSERT INTO TABLEB (@Code)But to me, this looks like a recipe for disaster.
ALTER TRIGGER Trg_Code ON TABLEB
INSTEAD OF INSERT AS
BEGINDECLARE @CompetitorCode nVarchar(250)
SELECT @CompetitorCode = M.Code From TABLEA AS [M]
INNER JOIN INSERTED I ON M.Code = I.CompetitorType_Code
BEGIN TRY
IF (@Code is Not NULL)
INSERT INTO TABLEB(CompetitorType_Code)
Select @CompetitorCode
END TRY
BEGIN CATCH
IF (@CompetitorCode is NULL)BEGIN
RAISERROR (N'The provided CompetitorTypeCode does not exist in TableB',16, 1);
ROLLBACK TRANSACTION;
RETURN
END
END CATCH
ENDI added try catch block and it does not do what it has to do. Can you please check the code?
I noticed my previous comment was wrong about the INSERT INTO TABLEB(@code).
but your code line:IF (@Code is Not NULL)
Is the problem. It should be @CompetitorCode.But I am still concerned about infinite loops. Do you have "Allow Triggers to Fire Others" set to true? If so, you might get hit by infinite trigger loops.
This inserts the value but not raising any error if the code is incorrect.
February 13, 2017 at 12:59 pm
komal145 - Monday, February 13, 2017 12:54 PMbmg002 - Monday, February 13, 2017 12:49 PMkomal145 - Monday, February 13, 2017 12:43 PMbmg002 - Monday, February 13, 2017 10:26 AMThere is also some weirdness with your code. Your:SELECT @code
FROM inserted
line is strange. You don't need a FROM when you are just selecting the variable.But I agree, a Foreign Key Constraint is a much safer way to go and then use a try-catch block in your stored procedure that does the insert.
And as John pointed out, the code:
SELECT @Code = Code from TABLEA AS [M]
INNER JOIN INSERTED I ON M.Code = I.CODE
will fail if inserted contains more than 1 row AND more than 1 code matches.A FK would be much faster for your inserts as well and you could get stuck in an infinite loop depending on your settings.
BUT if the code you provided is what you want, change this line:
INSERT INTO TABLEB (Code)
toINSERT INTO TABLEB (@Code)But to me, this looks like a recipe for disaster.
ALTER TRIGGER Trg_Code ON TABLEB
INSTEAD OF INSERT AS
BEGINDECLARE @CompetitorCode nVarchar(250)
SELECT @CompetitorCode = M.Code From TABLEA AS [M]
INNER JOIN INSERTED I ON M.Code = I.CompetitorType_Code
BEGIN TRY
IF (@Code is Not NULL)
INSERT INTO TABLEB(CompetitorType_Code)
Select @CompetitorCode
END TRY
BEGIN CATCH
IF (@CompetitorCode is NULL)BEGIN
RAISERROR (N'The provided CompetitorTypeCode does not exist in TableB',16, 1);
ROLLBACK TRANSACTION;
RETURN
END
END CATCH
ENDI added try catch block and it does not do what it has to do. Can you please check the code?
I noticed my previous comment was wrong about the INSERT INTO TABLEB(@code).
but your code line:IF (@Code is Not NULL)
Is the problem. It should be @CompetitorCode.But I am still concerned about infinite loops. Do you have "Allow Triggers to Fire Others" set to true? If so, you might get hit by infinite trigger loops.
This inserts the value but not raising any error if the code is incorrect.
OK. So you are making progress.
The raiserror will report an error in the SQL Log. You sure there is no errors apeparing in the SQL Log after raiseerror?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 13, 2017 at 1:00 pm
bmg002 - Monday, February 13, 2017 12:59 PMkomal145 - Monday, February 13, 2017 12:54 PMbmg002 - Monday, February 13, 2017 12:49 PMkomal145 - Monday, February 13, 2017 12:43 PMbmg002 - Monday, February 13, 2017 10:26 AMThere is also some weirdness with your code. Your:SELECT @code
FROM inserted
line is strange. You don't need a FROM when you are just selecting the variable.But I agree, a Foreign Key Constraint is a much safer way to go and then use a try-catch block in your stored procedure that does the insert.
And as John pointed out, the code:
SELECT @Code = Code from TABLEA AS [M]
INNER JOIN INSERTED I ON M.Code = I.CODE
will fail if inserted contains more than 1 row AND more than 1 code matches.A FK would be much faster for your inserts as well and you could get stuck in an infinite loop depending on your settings.
BUT if the code you provided is what you want, change this line:
INSERT INTO TABLEB (Code)
toINSERT INTO TABLEB (@Code)But to me, this looks like a recipe for disaster.
ALTER TRIGGER Trg_Code ON TABLEB
INSTEAD OF INSERT AS
BEGINDECLARE @CompetitorCode nVarchar(250)
SELECT @CompetitorCode = M.Code From TABLEA AS [M]
INNER JOIN INSERTED I ON M.Code = I.CompetitorType_Code
BEGIN TRY
IF (@Code is Not NULL)
INSERT INTO TABLEB(CompetitorType_Code)
Select @CompetitorCode
END TRY
BEGIN CATCH
IF (@CompetitorCode is NULL)BEGIN
RAISERROR (N'The provided CompetitorTypeCode does not exist in TableB',16, 1);
ROLLBACK TRANSACTION;
RETURN
END
END CATCH
ENDI added try catch block and it does not do what it has to do. Can you please check the code?
I noticed my previous comment was wrong about the INSERT INTO TABLEB(@code).
but your code line:IF (@Code is Not NULL)
Is the problem. It should be @CompetitorCode.But I am still concerned about infinite loops. Do you have "Allow Triggers to Fire Others" set to true? If so, you might get hit by infinite trigger loops.
This inserts the value but not raising any error if the code is incorrect.
OK. So you are making progress.
The raiserror will report an error in the SQL Log. You sure there is no errors apeparing in the SQL Log after raiseerror?
yes. No errors appearing.
February 13, 2017 at 1:03 pm
komal145 - Monday, February 13, 2017 1:00 PMbmg002 - Monday, February 13, 2017 12:59 PMkomal145 - Monday, February 13, 2017 12:54 PMbmg002 - Monday, February 13, 2017 12:49 PMkomal145 - Monday, February 13, 2017 12:43 PMbmg002 - Monday, February 13, 2017 10:26 AMThere is also some weirdness with your code. Your:SELECT @code
FROM inserted
line is strange. You don't need a FROM when you are just selecting the variable.But I agree, a Foreign Key Constraint is a much safer way to go and then use a try-catch block in your stored procedure that does the insert.
And as John pointed out, the code:
SELECT @Code = Code from TABLEA AS [M]
INNER JOIN INSERTED I ON M.Code = I.CODE
will fail if inserted contains more than 1 row AND more than 1 code matches.A FK would be much faster for your inserts as well and you could get stuck in an infinite loop depending on your settings.
BUT if the code you provided is what you want, change this line:
INSERT INTO TABLEB (Code)
toINSERT INTO TABLEB (@Code)But to me, this looks like a recipe for disaster.
ALTER TRIGGER Trg_Code ON TABLEB
INSTEAD OF INSERT AS
BEGINDECLARE @CompetitorCode nVarchar(250)
SELECT @CompetitorCode = M.Code From TABLEA AS [M]
INNER JOIN INSERTED I ON M.Code = I.CompetitorType_Code
BEGIN TRY
IF (@Code is Not NULL)
INSERT INTO TABLEB(CompetitorType_Code)
Select @CompetitorCode
END TRY
BEGIN CATCH
IF (@CompetitorCode is NULL)BEGIN
RAISERROR (N'The provided CompetitorTypeCode does not exist in TableB',16, 1);
ROLLBACK TRANSACTION;
RETURN
END
END CATCH
ENDI added try catch block and it does not do what it has to do. Can you please check the code?
I noticed my previous comment was wrong about the INSERT INTO TABLEB(@code).
but your code line:IF (@Code is Not NULL)
Is the problem. It should be @CompetitorCode.But I am still concerned about infinite loops. Do you have "Allow Triggers to Fire Others" set to true? If so, you might get hit by infinite trigger loops.
This inserts the value but not raising any error if the code is incorrect.
OK. So you are making progress.
The raiserror will report an error in the SQL Log. You sure there is no errors apeparing in the SQL Log after raiseerror?yes. No errors appearing.
I am using custom error message. It should show when insert wrong code. Not log
February 13, 2017 at 1:05 pm
komal145 - Monday, February 13, 2017 1:00 PMbmg002 - Monday, February 13, 2017 12:59 PMkomal145 - Monday, February 13, 2017 12:54 PMbmg002 - Monday, February 13, 2017 12:49 PMkomal145 - Monday, February 13, 2017 12:43 PMbmg002 - Monday, February 13, 2017 10:26 AMThere is also some weirdness with your code. Your:SELECT @code
FROM inserted
line is strange. You don't need a FROM when you are just selecting the variable.But I agree, a Foreign Key Constraint is a much safer way to go and then use a try-catch block in your stored procedure that does the insert.
And as John pointed out, the code:
SELECT @Code = Code from TABLEA AS [M]
INNER JOIN INSERTED I ON M.Code = I.CODE
will fail if inserted contains more than 1 row AND more than 1 code matches.A FK would be much faster for your inserts as well and you could get stuck in an infinite loop depending on your settings.
BUT if the code you provided is what you want, change this line:
INSERT INTO TABLEB (Code)
toINSERT INTO TABLEB (@Code)But to me, this looks like a recipe for disaster.
ALTER TRIGGER Trg_Code ON TABLEB
INSTEAD OF INSERT AS
BEGINDECLARE @CompetitorCode nVarchar(250)
SELECT @CompetitorCode = M.Code From TABLEA AS [M]
INNER JOIN INSERTED I ON M.Code = I.CompetitorType_Code
BEGIN TRY
IF (@Code is Not NULL)
INSERT INTO TABLEB(CompetitorType_Code)
Select @CompetitorCode
END TRY
BEGIN CATCH
IF (@CompetitorCode is NULL)BEGIN
RAISERROR (N'The provided CompetitorTypeCode does not exist in TableB',16, 1);
ROLLBACK TRANSACTION;
RETURN
END
END CATCH
ENDI added try catch block and it does not do what it has to do. Can you please check the code?
I noticed my previous comment was wrong about the INSERT INTO TABLEB(@code).
but your code line:IF (@Code is Not NULL)
Is the problem. It should be @CompetitorCode.But I am still concerned about infinite loops. Do you have "Allow Triggers to Fire Others" set to true? If so, you might get hit by infinite trigger loops.
This inserts the value but not raising any error if the code is incorrect.
OK. So you are making progress.
The raiserror will report an error in the SQL Log. You sure there is no errors apeparing in the SQL Log after raiseerror?yes. No errors appearing.
Try putting the raiseerror after your rollback. Not sure if that'll make a difference, but I'm wondering if it does.
if it makes no difference, can you verify that the competitor code is null (ie print it out)?
Also, what is SQL telling you when you try to insert invalid data? What message is being printed to screen?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 13, 2017 at 1:15 pm
bmg002 - Monday, February 13, 2017 1:05 PMkomal145 - Monday, February 13, 2017 1:00 PMbmg002 - Monday, February 13, 2017 12:59 PMkomal145 - Monday, February 13, 2017 12:54 PMbmg002 - Monday, February 13, 2017 12:49 PMkomal145 - Monday, February 13, 2017 12:43 PMbmg002 - Monday, February 13, 2017 10:26 AMThere is also some weirdness with your code. Your:SELECT @code
FROM inserted
line is strange. You don't need a FROM when you are just selecting the variable.But I agree, a Foreign Key Constraint is a much safer way to go and then use a try-catch block in your stored procedure that does the insert.
And as John pointed out, the code:
SELECT @Code = Code from TABLEA AS [M]
INNER JOIN INSERTED I ON M.Code = I.CODE
will fail if inserted contains more than 1 row AND more than 1 code matches.A FK would be much faster for your inserts as well and you could get stuck in an infinite loop depending on your settings.
BUT if the code you provided is what you want, change this line:
INSERT INTO TABLEB (Code)
toINSERT INTO TABLEB (@Code)But to me, this looks like a recipe for disaster.
ALTER TRIGGER Trg_Code ON TABLEB
INSTEAD OF INSERT AS
BEGINDECLARE @CompetitorCode nVarchar(250)
SELECT @CompetitorCode = M.Code From TABLEA AS [M]
INNER JOIN INSERTED I ON M.Code = I.CompetitorType_Code
BEGIN TRY
IF (@Code is Not NULL)
INSERT INTO TABLEB(CompetitorType_Code)
Select @CompetitorCode
END TRY
BEGIN CATCH
IF (@CompetitorCode is NULL)BEGIN
RAISERROR (N'The provided CompetitorTypeCode does not exist in TableB',16, 1);
ROLLBACK TRANSACTION;
RETURN
END
END CATCH
ENDI added try catch block and it does not do what it has to do. Can you please check the code?
I noticed my previous comment was wrong about the INSERT INTO TABLEB(@code).
but your code line:IF (@Code is Not NULL)
Is the problem. It should be @CompetitorCode.But I am still concerned about infinite loops. Do you have "Allow Triggers to Fire Others" set to true? If so, you might get hit by infinite trigger loops.
This inserts the value but not raising any error if the code is incorrect.
OK. So you are making progress.
The raiserror will report an error in the SQL Log. You sure there is no errors apeparing in the SQL Log after raiseerror?yes. No errors appearing.
Try putting the raiseerror after your rollback. Not sure if that'll make a difference, but I'm wondering if it does.
if it makes no difference, can you verify that the competitor code is null (ie print it out)?
I tired ,to print out the @competitorcode before try block( it prints) inside catch....i added print @competitorcode doe snot print anyything
February 13, 2017 at 1:19 pm
komal145 - Monday, February 13, 2017 1:15 PMbmg002 - Monday, February 13, 2017 1:05 PMkomal145 - Monday, February 13, 2017 1:00 PMbmg002 - Monday, February 13, 2017 12:59 PMkomal145 - Monday, February 13, 2017 12:54 PMbmg002 - Monday, February 13, 2017 12:49 PMkomal145 - Monday, February 13, 2017 12:43 PMbmg002 - Monday, February 13, 2017 10:26 AMThere is also some weirdness with your code. Your:SELECT @code
FROM inserted
line is strange. You don't need a FROM when you are just selecting the variable.But I agree, a Foreign Key Constraint is a much safer way to go and then use a try-catch block in your stored procedure that does the insert.
And as John pointed out, the code:
SELECT @Code = Code from TABLEA AS [M]
INNER JOIN INSERTED I ON M.Code = I.CODE
will fail if inserted contains more than 1 row AND more than 1 code matches.A FK would be much faster for your inserts as well and you could get stuck in an infinite loop depending on your settings.
BUT if the code you provided is what you want, change this line:
INSERT INTO TABLEB (Code)
toINSERT INTO TABLEB (@Code)But to me, this looks like a recipe for disaster.
ALTER TRIGGER Trg_Code ON TABLEB
INSTEAD OF INSERT AS
BEGINDECLARE @CompetitorCode nVarchar(250)
SELECT @CompetitorCode = M.Code From TABLEA AS [M]
INNER JOIN INSERTED I ON M.Code = I.CompetitorType_Code
BEGIN TRY
IF (@Code is Not NULL)
INSERT INTO TABLEB(CompetitorType_Code)
Select @CompetitorCode
END TRY
BEGIN CATCH
IF (@CompetitorCode is NULL)BEGIN
RAISERROR (N'The provided CompetitorTypeCode does not exist in TableB',16, 1);
ROLLBACK TRANSACTION;
RETURN
END
END CATCH
ENDI added try catch block and it does not do what it has to do. Can you please check the code?
I noticed my previous comment was wrong about the INSERT INTO TABLEB(@code).
but your code line:IF (@Code is Not NULL)
Is the problem. It should be @CompetitorCode.But I am still concerned about infinite loops. Do you have "Allow Triggers to Fire Others" set to true? If so, you might get hit by infinite trigger loops.
This inserts the value but not raising any error if the code is incorrect.
OK. So you are making progress.
The raiserror will report an error in the SQL Log. You sure there is no errors apeparing in the SQL Log after raiseerror?yes. No errors appearing.
Try putting the raiseerror after your rollback. Not sure if that'll make a difference, but I'm wondering if it does.
if it makes no difference, can you verify that the competitor code is null (ie print it out)?
I tired ,to print out the @competitorcode before try block( it prints) inside catch....i added print @competitorcode doe snot print anyything
It doesnot print for error ..for correct code it prints code.
The catch block is not working
February 13, 2017 at 1:23 pm
komal145 - Monday, February 13, 2017 1:19 PMkomal145 - Monday, February 13, 2017 1:15 PMbmg002 - Monday, February 13, 2017 1:05 PMkomal145 - Monday, February 13, 2017 1:00 PMbmg002 - Monday, February 13, 2017 12:59 PMkomal145 - Monday, February 13, 2017 12:54 PMbmg002 - Monday, February 13, 2017 12:49 PMkomal145 - Monday, February 13, 2017 12:43 PMbmg002 - Monday, February 13, 2017 10:26 AMThere is also some weirdness with your code. Your:SELECT @code
FROM inserted
line is strange. You don't need a FROM when you are just selecting the variable.But I agree, a Foreign Key Constraint is a much safer way to go and then use a try-catch block in your stored procedure that does the insert.
And as John pointed out, the code:
SELECT @Code = Code from TABLEA AS [M]
INNER JOIN INSERTED I ON M.Code = I.CODE
will fail if inserted contains more than 1 row AND more than 1 code matches.A FK would be much faster for your inserts as well and you could get stuck in an infinite loop depending on your settings.
BUT if the code you provided is what you want, change this line:
INSERT INTO TABLEB (Code)
toINSERT INTO TABLEB (@Code)But to me, this looks like a recipe for disaster.
ALTER TRIGGER Trg_Code ON TABLEB
INSTEAD OF INSERT AS
BEGINDECLARE @CompetitorCode nVarchar(250)
SELECT @CompetitorCode = M.Code From TABLEA AS [M]
INNER JOIN INSERTED I ON M.Code = I.CompetitorType_Code
BEGIN TRY
IF (@Code is Not NULL)
INSERT INTO TABLEB(CompetitorType_Code)
Select @CompetitorCode
END TRY
BEGIN CATCH
IF (@CompetitorCode is NULL)BEGIN
RAISERROR (N'The provided CompetitorTypeCode does not exist in TableB',16, 1);
ROLLBACK TRANSACTION;
RETURN
END
END CATCH
ENDI added try catch block and it does not do what it has to do. Can you please check the code?
I noticed my previous comment was wrong about the INSERT INTO TABLEB(@code).
but your code line:IF (@Code is Not NULL)
Is the problem. It should be @CompetitorCode.But I am still concerned about infinite loops. Do you have "Allow Triggers to Fire Others" set to true? If so, you might get hit by infinite trigger loops.
This inserts the value but not raising any error if the code is incorrect.
OK. So you are making progress.
The raiserror will report an error in the SQL Log. You sure there is no errors apeparing in the SQL Log after raiseerror?yes. No errors appearing.
Try putting the raiseerror after your rollback. Not sure if that'll make a difference, but I'm wondering if it does.
if it makes no difference, can you verify that the competitor code is null (ie print it out)?
I tired ,to print out the @competitorcode before try block( it prints) inside catch....i added print @competitorcode doe snot print anyything
It doesnot print for error ..for correct code it prints code.
The catch block is not working
I just realized why. I am dumb.
It is because the TRY is succeeding. No error is being thrown inside the TRY so the TRY succeeds and the catch never gets called.
Toss your raiseerror inside your TRY as:BEGIN TRY
IF (@Code is Not NULL)
BEGIN
INSERT INTO TABLEB(CompetitorType_Code)
Select @CompetitorCode
END
IF (@CompetitorCode is NULL)
RAISERROR (N'The provided CompetitorTypeCode does not exist in TableB',16, 1);
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
RETURN
END CATCH
Don't think you need the TRY CATCH block though... it feels a little messy to me. But I bet that'll give you what you want.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply