Mixing
Trigger that fires a rule is erroring
0
I have a trigger that calls a database procedure which logs all changes to every column in a particular table but when the trigger fires I get an error
Msg 8114, Level 16, State 5, Procedure dp_PolicyBenefit_audit, Line 0
Error converting data type nvarchar to int.
I have an INSERT and UPDATE trigger for every column in PolicyBenefit, 100 in all - the code below shows just one
drop procedure if exists dp_PolicyBenefit_audit
GO
create procedure dp_PolicyBenefit_audit
@p_policy_id varchar(20),
@p_id int,
@p_benefit_cd char(4),
@p_seq smallint,
@p_operation varchar(10),
@p_description varchar(100),
@p_old_value varchar(20),
@p_new_value varchar(20)
AS
if (@p_operation = 'INSERT')
begin
insert into PolicyBenefitAudit
(
PolicyNo,
EntityNo,
BenefitCode,
BenefitSequence,
Description,
OldValue,
NewValue,
CreatedBy,
CreatedDate
)
values
(
@p_policy_id,
@p_id,
@p_benefit_cd,
@p_seq,
@p_description,
null,
@p_new_value,
user_name(),
getdate()
)
end
if (@p_operation = 'UPDATE') and isnull(@p_old_value,'') != isnull(@p_new_value,''))
begin
insert into PolicyBenefitAudit
(
PolicyNo,
EntityNo,
BenefitCode,
BenefitSequence,
Description,
OldValue,
NewValue,
CreatedBy,
CreatedDate
)
values
(
@p_policy_id,
@p_id,
@p_benefit_cd,
@p_seq,
@p_description,
@p_old_value,
@p_new_value,
user_name(),
getdate()
)
end
GO
drop trigger if exists dt_PolicyBenefit_OccupationCode_upd
GO
create trigger dt_PolicyBenefit_OccupationCode_upd on PolicyBenefit
after update as
begin
declare
@old_OccupationCode varchar(100)
select @old_OccupationCode = cast(OccupationCode as varchar(100)) from deleted
exec dp_PolicyBenefit_audit @p_policy_id = PolicyNo,
@p_id = EntityNo,
@p_benefit_cd = BenefitCode,
@p_seq = BenefitSequence,
@p_operation = 'UPDATE',
@p_description = 'Occupational Per Mille loading duration (in years)',
@p_old_value = @old_OccupationCode,
@p_new_value = OccupationCode
end
GO
Expecting procedure to insert a row into the PolicyBenefitAudit table
sql-server procedure eventtrigger edited Dec 20 '18 at 5:00
marc_s
582k13011221268
asked Dec 20 '18 at 4:58
Colin HayColin Hay
212
|
show 1 more comment
0
I have a trigger that calls a database procedure which logs all changes to every column in a particular table but when the trigger fires I get an error
Msg 8114, Level 16, State 5, Procedure dp_PolicyBenefit_audit, Line 0
Error converting data type nvarchar to int.
I have an INSERT and UPDATE trigger for every column in PolicyBenefit, 100 in all - the code below shows just one
drop procedure if exists dp_PolicyBenefit_audit
GO
create procedure dp_PolicyBenefit_audit
@p_policy_id varchar(20),
@p_id int,
@p_benefit_cd char(4),
@p_seq smallint,
@p_operation varchar(10),
@p_description varchar(100),
@p_old_value varchar(20),
@p_new_value varchar(20)
AS
if (@p_operation = 'INSERT')
begin
insert into PolicyBenefitAudit
(
PolicyNo,
EntityNo,
BenefitCode,
BenefitSequence,
Description,
OldValue,
NewValue,
CreatedBy,
CreatedDate
)
values
(
@p_policy_id,
@p_id,
@p_benefit_cd,
@p_seq,
@p_description,
null,
@p_new_value,
user_name(),
getdate()
)
end
if (@p_operation = 'UPDATE') and isnull(@p_old_value,'') != isnull(@p_new_value,''))
begin
insert into PolicyBenefitAudit
(
PolicyNo,
EntityNo,
BenefitCode,
BenefitSequence,
Description,
OldValue,
NewValue,
CreatedBy,
CreatedDate
)
values
(
@p_policy_id,
@p_id,
@p_benefit_cd,
@p_seq,
@p_description,
@p_old_value,
@p_new_value,
user_name(),
getdate()
)
end
GO
drop trigger if exists dt_PolicyBenefit_OccupationCode_upd
GO
create trigger dt_PolicyBenefit_OccupationCode_upd on PolicyBenefit
after update as
begin
declare
@old_OccupationCode varchar(100)
select @old_OccupationCode = cast(OccupationCode as varchar(100)) from deleted
exec dp_PolicyBenefit_audit @p_policy_id = PolicyNo,
@p_id = EntityNo,
@p_benefit_cd = BenefitCode,
@p_seq = BenefitSequence,
@p_operation = 'UPDATE',
@p_description = 'Occupational Per Mille loading duration (in years)',
@p_old_value = @old_OccupationCode,
@p_new_value = OccupationCode
end
GO
Expecting procedure to insert a row into the PolicyBenefitAudit table
sql-server procedure eventtrigger edited Dec 20 '18 at 5:00
marc_s
582k13011221268
asked Dec 20 '18 at 4:58
Colin HayColin Hay
212
1
the error message is very clear. Double check your source & destination column data type.
– Squirrel
Dec 20 '18 at 5:18
You are inserting a string value for an 'int' field.
– Thilina Nakkawita
Dec 20 '18 at 6:11
Start over. No one can help you debug code that is syntactically incorrect and cannot be created. Your trigger attempts to pass values to the procedure that do not exist - OccupationCode, BenefitSequence, EntityNo, PolicyNo. In addition, you just are not paying attention - is Occupation Code varchar 100 (as defined in trigger) or varchar 20 (as defined in procedure - which a pointlessly vague name)?
– SMor
Dec 20 '18 at 13:12
And you make the classic mistake of assuming a single row is affected by the statement that executes the trigger. There might be zero rows, 1 row, or many, many rows.
– SMor
Dec 20 '18 at 13:13
Solved: The syntax is fine - OccupationCode, BenefitSequence, EntityNo and PolicyNo are all columns NOT parameters. The varchar(100) should be varchar(20) but thats not the problem either. The issue was the parameter p_type should have been p_benefit_cd - a rename to due our conversion of our INGRES DBMS to SQLServer .... Also if there are multiple rows I want multiple rows inserted
– Colin Hay
Dec 21 '18 at 5:37
|
show 1 more comment
0
0
0
I have a trigger that calls a database procedure which logs all changes to every column in a particular table but when the trigger fires I get an error
Msg 8114, Level 16, State 5, Procedure dp_PolicyBenefit_audit, Line 0
Error converting data type nvarchar to int.
I have an INSERT and UPDATE trigger for every column in PolicyBenefit, 100 in all - the code below shows just one
drop procedure if exists dp_PolicyBenefit_audit
GO
create procedure dp_PolicyBenefit_audit
@p_policy_id varchar(20),
@p_id int,
@p_benefit_cd char(4),
@p_seq smallint,
@p_operation varchar(10),
@p_description varchar(100),
@p_old_value varchar(20),
@p_new_value varchar(20)
AS
if (@p_operation = 'INSERT')
begin
insert into PolicyBenefitAudit
(
PolicyNo,
EntityNo,
BenefitCode,
BenefitSequence,
Description,
OldValue,
NewValue,
CreatedBy,
CreatedDate
)
values
(
@p_policy_id,
@p_id,
@p_benefit_cd,
@p_seq,
@p_description,
null,
@p_new_value,
user_name(),
getdate()
)
end
if (@p_operation = 'UPDATE') and isnull(@p_old_value,'') != isnull(@p_new_value,''))
begin
insert into PolicyBenefitAudit
(
PolicyNo,
EntityNo,
BenefitCode,
BenefitSequence,
Description,
OldValue,
NewValue,
CreatedBy,
CreatedDate
)
values
(
@p_policy_id,
@p_id,
@p_benefit_cd,
@p_seq,
@p_description,
@p_old_value,
@p_new_value,
user_name(),
getdate()
)
end
GO
drop trigger if exists dt_PolicyBenefit_OccupationCode_upd
GO
create trigger dt_PolicyBenefit_OccupationCode_upd on PolicyBenefit
after update as
begin
declare
@old_OccupationCode varchar(100)
select @old_OccupationCode = cast(OccupationCode as varchar(100)) from deleted
exec dp_PolicyBenefit_audit @p_policy_id = PolicyNo,
@p_id = EntityNo,
@p_benefit_cd = BenefitCode,
@p_seq = BenefitSequence,
@p_operation = 'UPDATE',
@p_description = 'Occupational Per Mille loading duration (in years)',
@p_old_value = @old_OccupationCode,
@p_new_value = OccupationCode
end
GO
Expecting procedure to insert a row into the PolicyBenefitAudit table
sql-server procedure eventtrigger edited Dec 20 '18 at 5:00
marc_s
582k13011221268
asked Dec 20 '18 at 4:58
Colin HayColin Hay
212
I have a trigger that calls a database procedure which logs all changes to every column in a particular table but when the trigger fires I get an error
Msg 8114, Level 16, State 5, Procedure dp_PolicyBenefit_audit, Line 0
Error converting data type nvarchar to int.
I have an INSERT and UPDATE trigger for every column in PolicyBenefit, 100 in all - the code below shows just one
drop procedure if exists dp_PolicyBenefit_audit
GO
create procedure dp_PolicyBenefit_audit
@p_policy_id varchar(20),
@p_id int,
@p_benefit_cd char(4),
@p_seq smallint,
@p_operation varchar(10),
@p_description varchar(100),
@p_old_value varchar(20),
@p_new_value varchar(20)
AS
if (@p_operation = 'INSERT')
begin
insert into PolicyBenefitAudit
(
PolicyNo,
EntityNo,
BenefitCode,
BenefitSequence,
Description,
OldValue,
NewValue,
CreatedBy,
CreatedDate
)
values
(
@p_policy_id,
@p_id,
@p_benefit_cd,
@p_seq,
@p_description,
null,
@p_new_value,
user_name(),
getdate()
)
end
if (@p_operation = 'UPDATE') and isnull(@p_old_value,'') != isnull(@p_new_value,''))
begin
insert into PolicyBenefitAudit
(
PolicyNo,
EntityNo,
BenefitCode,
BenefitSequence,
Description,
OldValue,
NewValue,
CreatedBy,
CreatedDate
)
values
(
@p_policy_id,
@p_id,
@p_benefit_cd,
@p_seq,
@p_description,
@p_old_value,
@p_new_value,
user_name(),
getdate()
)
end
GO
drop trigger if exists dt_PolicyBenefit_OccupationCode_upd
GO
create trigger dt_PolicyBenefit_OccupationCode_upd on PolicyBenefit
after update as
begin
declare
@old_OccupationCode varchar(100)
select @old_OccupationCode = cast(OccupationCode as varchar(100)) from deleted
exec dp_PolicyBenefit_audit @p_policy_id = PolicyNo,
@p_id = EntityNo,
@p_benefit_cd = BenefitCode,
@p_seq = BenefitSequence,
@p_operation = 'UPDATE',
@p_description = 'Occupational Per Mille loading duration (in years)',
@p_old_value = @old_OccupationCode,
@p_new_value = OccupationCode
end
GO
Expecting procedure to insert a row into the PolicyBenefitAudit table
sql-server procedure eventtrigger sql-server procedure eventtrigger edited Dec 20 '18 at 5:00
marc_s
582k13011221268
asked Dec 20 '18 at 4:58
Colin HayColin Hay
212
edited Dec 20 '18 at 5:00
marc_s
582k13011221268
asked Dec 20 '18 at 4:58
Colin HayColin Hay
212
edited Dec 20 '18 at 5:00
marc_s
582k13011221268
edited Dec 20 '18 at 5:00
marc_s
582k13011221268
edited Dec 20 '18 at 5:00
marc_s
582k13011221268
582k13011221268
asked Dec 20 '18 at 4:58
Colin HayColin Hay
212
asked Dec 20 '18 at 4:58
Colin HayColin Hay
212
asked Dec 20 '18 at 4:58
Colin HayColin Hay
212
212
1
the error message is very clear. Double check your source & destination column data type.
– Squirrel
Dec 20 '18 at 5:18
You are inserting a string value for an 'int' field.
– Thilina Nakkawita
Dec 20 '18 at 6:11
Start over. No one can help you debug code that is syntactically incorrect and cannot be created. Your trigger attempts to pass values to the procedure that do not exist - OccupationCode, BenefitSequence, EntityNo, PolicyNo. In addition, you just are not paying attention - is Occupation Code varchar 100 (as defined in trigger) or varchar 20 (as defined in procedure - which a pointlessly vague name)?
– SMor
Dec 20 '18 at 13:12
And you make the classic mistake of assuming a single row is affected by the statement that executes the trigger. There might be zero rows, 1 row, or many, many rows.
– SMor
Dec 20 '18 at 13:13
Solved: The syntax is fine - OccupationCode, BenefitSequence, EntityNo and PolicyNo are all columns NOT parameters. The varchar(100) should be varchar(20) but thats not the problem either. The issue was the parameter p_type should have been p_benefit_cd - a rename to due our conversion of our INGRES DBMS to SQLServer .... Also if there are multiple rows I want multiple rows inserted
– Colin Hay
Dec 21 '18 at 5:37
|
show 1 more comment
1
the error message is very clear. Double check your source & destination column data type.
– Squirrel
Dec 20 '18 at 5:18
You are inserting a string value for an 'int' field.
– Thilina Nakkawita
Dec 20 '18 at 6:11
Start over. No one can help you debug code that is syntactically incorrect and cannot be created. Your trigger attempts to pass values to the procedure that do not exist - OccupationCode, BenefitSequence, EntityNo, PolicyNo. In addition, you just are not paying attention - is Occupation Code varchar 100 (as defined in trigger) or varchar 20 (as defined in procedure - which a pointlessly vague name)?
– SMor
Dec 20 '18 at 13:12
And you make the classic mistake of assuming a single row is affected by the statement that executes the trigger. There might be zero rows, 1 row, or many, many rows.
– SMor
Dec 20 '18 at 13:13
Solved: The syntax is fine - OccupationCode, BenefitSequence, EntityNo and PolicyNo are all columns NOT parameters. The varchar(100) should be varchar(20) but thats not the problem either. The issue was the parameter p_type should have been p_benefit_cd - a rename to due our conversion of our INGRES DBMS to SQLServer .... Also if there are multiple rows I want multiple rows inserted
– Colin Hay
Dec 21 '18 at 5:37
1
1
the error message is very clear. Double check your source & destination column data type.
– Squirrel
Dec 20 '18 at 5:18
the error message is very clear. Double check your source & destination column data type.
– Squirrel
Dec 20 '18 at 5:18
You are inserting a string value for an 'int' field.
– Thilina Nakkawita
Dec 20 '18 at 6:11
You are inserting a string value for an 'int' field.
– Thilina Nakkawita
Dec 20 '18 at 6:11
Start over. No one can help you debug code that is syntactically incorrect and cannot be created. Your trigger attempts to pass values to the procedure that do not exist - OccupationCode, BenefitSequence, EntityNo, PolicyNo. In addition, you just are not paying attention - is Occupation Code varchar 100 (as defined in trigger) or varchar 20 (as defined in procedure - which a pointlessly vague name)?
– SMor
Dec 20 '18 at 13:12
Start over. No one can help you debug code that is syntactically incorrect and cannot be created. Your trigger attempts to pass values to the procedure that do not exist - OccupationCode, BenefitSequence, EntityNo, PolicyNo. In addition, you just are not paying attention - is Occupation Code varchar 100 (as defined in trigger) or varchar 20 (as defined in procedure - which a pointlessly vague name)?
– SMor
Dec 20 '18 at 13:12
And you make the classic mistake of assuming a single row is affected by the statement that executes the trigger. There might be zero rows, 1 row, or many, many rows.
– SMor
Dec 20 '18 at 13:13
And you make the classic mistake of assuming a single row is affected by the statement that executes the trigger. There might be zero rows, 1 row, or many, many rows.
– SMor
Dec 20 '18 at 13:13
Solved: The syntax is fine - OccupationCode, BenefitSequence, EntityNo and PolicyNo are all columns NOT parameters. The varchar(100) should be varchar(20) but thats not the problem either. The issue was the parameter p_type should have been p_benefit_cd - a rename to due our conversion of our INGRES DBMS to SQLServer .... Also if there are multiple rows I want multiple rows inserted
– Colin Hay
Dec 21 '18 at 5:37
Solved: The syntax is fine - OccupationCode, BenefitSequence, EntityNo and PolicyNo are all columns NOT parameters. The varchar(100) should be varchar(20) but thats not the problem either. The issue was the parameter p_type should have been p_benefit_cd - a rename to due our conversion of our INGRES DBMS to SQLServer .... Also if there are multiple rows I want multiple rows inserted
– Colin Hay
Dec 21 '18 at 5:37
|
show 1 more comment
2 Answers
2
active
oldest
votes
0
The error is in passing parameters into your sp:
exec dp_PolicyBenefit_audit @p_policy_id = PolicyNo,
@p_id = EntityNo,
@p_benefit_cd = BenefitCode,
@p_seq = BenefitSequence,
@p_operation = 'UPDATE',
@p_description = 'Occupational Per Mille loading duration (in years)',
@p_old_value = @old_OccupationCode,
@p_new_value = OccupationCode
Your @p_id is declared as int, and you pass EntityNo (string) to it.
This cause "Error converting data type nvarchar to int", EntityNo is nvarchar.
answered Dec 20 '18 at 7:21
sepupicsepupic
6,6281315
EntityNo is an int thats not the problem
– Colin Hay
Dec 21 '18 at 5:53
@Colin Hay You are wrong, your code@p_id = EntityNois equivalent to@p_id = 'EntityNo'since EntityNo is not a field but a string constant, even if it's not enclosed in " ' "
– sepupic
Dec 21 '18 at 7:00
add a comment |
0
Did away with database procedures all together and coded it in 50 triggers like this:
create trigger dt_PolicyBenefit_OccupationalPerMilleLoadingDuration on PolicyBenefit
after insert, update as
begin
insert into PolicyBenefitAudit
(
PolicyNo,
EntityNo,
BenefitCode,
BenefitSequence,
Description,
OldValue,
NewValue,
CreatedBy,
CreatedDate
)
select i.PolicyNo,
i.EntityNo,
i.BenefitCode,
i.BenefitSequence,
'Occupational Per Mille loading duration (in years)',
d.OccupationalPerMilleLoadingDuration,
i.OccupationalPerMilleLoadingDuration,
user_name(),
getdate()
from inserted i left join deleted d
on i.PolicyBenefitId = d.PolicyBenefitId
where i.OccupationalPerMilleLoadingDuration != d.OccupationalPerMilleLoadingDuration;
end
GO
answered Jan 2 at 4:53
Colin HayColin Hay
212
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53862622%2ftrigger-that-fires-a-rule-is-erroring%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
The error is in passing parameters into your sp:
exec dp_PolicyBenefit_audit @p_policy_id = PolicyNo,
@p_id = EntityNo,
@p_benefit_cd = BenefitCode,
@p_seq = BenefitSequence,
@p_operation = 'UPDATE',
@p_description = 'Occupational Per Mille loading duration (in years)',
@p_old_value = @old_OccupationCode,
@p_new_value = OccupationCode
Your @p_id is declared as int, and you pass EntityNo (string) to it.
This cause "Error converting data type nvarchar to int", EntityNo is nvarchar.
answered Dec 20 '18 at 7:21
sepupicsepupic
6,6281315
EntityNo is an int thats not the problem
– Colin Hay
Dec 21 '18 at 5:53
@Colin Hay You are wrong, your code@p_id = EntityNois equivalent to@p_id = 'EntityNo'since EntityNo is not a field but a string constant, even if it's not enclosed in " ' "
– sepupic
Dec 21 '18 at 7:00
add a comment |
0
The error is in passing parameters into your sp:
exec dp_PolicyBenefit_audit @p_policy_id = PolicyNo,
@p_id = EntityNo,
@p_benefit_cd = BenefitCode,
@p_seq = BenefitSequence,
@p_operation = 'UPDATE',
@p_description = 'Occupational Per Mille loading duration (in years)',
@p_old_value = @old_OccupationCode,
@p_new_value = OccupationCode
Your @p_id is declared as int, and you pass EntityNo (string) to it.
This cause "Error converting data type nvarchar to int", EntityNo is nvarchar.
answered Dec 20 '18 at 7:21
sepupicsepupic
6,6281315
EntityNo is an int thats not the problem
– Colin Hay
Dec 21 '18 at 5:53
@Colin Hay You are wrong, your code@p_id = EntityNois equivalent to@p_id = 'EntityNo'since EntityNo is not a field but a string constant, even if it's not enclosed in " ' "
– sepupic
Dec 21 '18 at 7:00
add a comment |
0
0
0
The error is in passing parameters into your sp:
exec dp_PolicyBenefit_audit @p_policy_id = PolicyNo,
@p_id = EntityNo,
@p_benefit_cd = BenefitCode,
@p_seq = BenefitSequence,
@p_operation = 'UPDATE',
@p_description = 'Occupational Per Mille loading duration (in years)',
@p_old_value = @old_OccupationCode,
@p_new_value = OccupationCode
Your @p_id is declared as int, and you pass EntityNo (string) to it.
This cause "Error converting data type nvarchar to int", EntityNo is nvarchar.
answered Dec 20 '18 at 7:21
sepupicsepupic
6,6281315
The error is in passing parameters into your sp:
exec dp_PolicyBenefit_audit @p_policy_id = PolicyNo,
@p_id = EntityNo,
@p_benefit_cd = BenefitCode,
@p_seq = BenefitSequence,
@p_operation = 'UPDATE',
@p_description = 'Occupational Per Mille loading duration (in years)',
@p_old_value = @old_OccupationCode,
@p_new_value = OccupationCode
Your @p_id is declared as int, and you pass EntityNo (string) to it.
This cause "Error converting data type nvarchar to int", EntityNo is nvarchar.
answered Dec 20 '18 at 7:21
sepupicsepupic
6,6281315
answered Dec 20 '18 at 7:21
sepupicsepupic
6,6281315
answered Dec 20 '18 at 7:21
sepupicsepupic
6,6281315
answered Dec 20 '18 at 7:21
sepupicsepupic
6,6281315
6,6281315
EntityNo is an int thats not the problem
– Colin Hay
Dec 21 '18 at 5:53
@Colin Hay You are wrong, your code@p_id = EntityNois equivalent to@p_id = 'EntityNo'since EntityNo is not a field but a string constant, even if it's not enclosed in " ' "
– sepupic
Dec 21 '18 at 7:00
add a comment |
EntityNo is an int thats not the problem
– Colin Hay
Dec 21 '18 at 5:53
@Colin Hay You are wrong, your code@p_id = EntityNois equivalent to@p_id = 'EntityNo'since EntityNo is not a field but a string constant, even if it's not enclosed in " ' "
– sepupic
Dec 21 '18 at 7:00
EntityNo is an int thats not the problem
– Colin Hay
Dec 21 '18 at 5:53
EntityNo is an int thats not the problem
– Colin Hay
Dec 21 '18 at 5:53
@Colin Hay You are wrong, your code
@p_id = EntityNo is equivalent to @p_id = 'EntityNo' since EntityNo is not a field but a string constant, even if it's not enclosed in " ' "– sepupic
Dec 21 '18 at 7:00
@Colin Hay You are wrong, your code
@p_id = EntityNo is equivalent to @p_id = 'EntityNo' since EntityNo is not a field but a string constant, even if it's not enclosed in " ' "– sepupic
Dec 21 '18 at 7:00
add a comment |
0
Did away with database procedures all together and coded it in 50 triggers like this:
create trigger dt_PolicyBenefit_OccupationalPerMilleLoadingDuration on PolicyBenefit
after insert, update as
begin
insert into PolicyBenefitAudit
(
PolicyNo,
EntityNo,
BenefitCode,
BenefitSequence,
Description,
OldValue,
NewValue,
CreatedBy,
CreatedDate
)
select i.PolicyNo,
i.EntityNo,
i.BenefitCode,
i.BenefitSequence,
'Occupational Per Mille loading duration (in years)',
d.OccupationalPerMilleLoadingDuration,
i.OccupationalPerMilleLoadingDuration,
user_name(),
getdate()
from inserted i left join deleted d
on i.PolicyBenefitId = d.PolicyBenefitId
where i.OccupationalPerMilleLoadingDuration != d.OccupationalPerMilleLoadingDuration;
end
GO
answered Jan 2 at 4:53
Colin HayColin Hay
212
add a comment |
0
Did away with database procedures all together and coded it in 50 triggers like this:
create trigger dt_PolicyBenefit_OccupationalPerMilleLoadingDuration on PolicyBenefit
after insert, update as
begin
insert into PolicyBenefitAudit
(
PolicyNo,
EntityNo,
BenefitCode,
BenefitSequence,
Description,
OldValue,
NewValue,
CreatedBy,
CreatedDate
)
select i.PolicyNo,
i.EntityNo,
i.BenefitCode,
i.BenefitSequence,
'Occupational Per Mille loading duration (in years)',
d.OccupationalPerMilleLoadingDuration,
i.OccupationalPerMilleLoadingDuration,
user_name(),
getdate()
from inserted i left join deleted d
on i.PolicyBenefitId = d.PolicyBenefitId
where i.OccupationalPerMilleLoadingDuration != d.OccupationalPerMilleLoadingDuration;
end
GO
answered Jan 2 at 4:53
Colin HayColin Hay
212
add a comment |
0
0
0
Did away with database procedures all together and coded it in 50 triggers like this:
create trigger dt_PolicyBenefit_OccupationalPerMilleLoadingDuration on PolicyBenefit
after insert, update as
begin
insert into PolicyBenefitAudit
(
PolicyNo,
EntityNo,
BenefitCode,
BenefitSequence,
Description,
OldValue,
NewValue,
CreatedBy,
CreatedDate
)
select i.PolicyNo,
i.EntityNo,
i.BenefitCode,
i.BenefitSequence,
'Occupational Per Mille loading duration (in years)',
d.OccupationalPerMilleLoadingDuration,
i.OccupationalPerMilleLoadingDuration,
user_name(),
getdate()
from inserted i left join deleted d
on i.PolicyBenefitId = d.PolicyBenefitId
where i.OccupationalPerMilleLoadingDuration != d.OccupationalPerMilleLoadingDuration;
end
GO
answered Jan 2 at 4:53
Colin HayColin Hay
212
Did away with database procedures all together and coded it in 50 triggers like this:
create trigger dt_PolicyBenefit_OccupationalPerMilleLoadingDuration on PolicyBenefit
after insert, update as
begin
insert into PolicyBenefitAudit
(
PolicyNo,
EntityNo,
BenefitCode,
BenefitSequence,
Description,
OldValue,
NewValue,
CreatedBy,
CreatedDate
)
select i.PolicyNo,
i.EntityNo,
i.BenefitCode,
i.BenefitSequence,
'Occupational Per Mille loading duration (in years)',
d.OccupationalPerMilleLoadingDuration,
i.OccupationalPerMilleLoadingDuration,
user_name(),
getdate()
from inserted i left join deleted d
on i.PolicyBenefitId = d.PolicyBenefitId
where i.OccupationalPerMilleLoadingDuration != d.OccupationalPerMilleLoadingDuration;
end
GO
answered Jan 2 at 4:53
Colin HayColin Hay
212
edited Feb 8 at 6:28
answered Jan 2 at 4:53
Colin HayColin Hay
212
answered Jan 2 at 4:53
Colin HayColin Hay
212
answered Jan 2 at 4:53
Colin HayColin Hay
212
212
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53862622%2ftrigger-that-fires-a-rule-is-erroring%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
the error message is very clear. Double check your source & destination column data type.
– Squirrel
Dec 20 '18 at 5:18
You are inserting a string value for an 'int' field.
– Thilina Nakkawita
Dec 20 '18 at 6:11
Start over. No one can help you debug code that is syntactically incorrect and cannot be created. Your trigger attempts to pass values to the procedure that do not exist - OccupationCode, BenefitSequence, EntityNo, PolicyNo. In addition, you just are not paying attention - is Occupation Code varchar 100 (as defined in trigger) or varchar 20 (as defined in procedure - which a pointlessly vague name)?
– SMor
Dec 20 '18 at 13:12
And you make the classic mistake of assuming a single row is affected by the statement that executes the trigger. There might be zero rows, 1 row, or many, many rows.
– SMor
Dec 20 '18 at 13:13
Solved: The syntax is fine - OccupationCode, BenefitSequence, EntityNo and PolicyNo are all columns NOT parameters. The varchar(100) should be varchar(20) but thats not the problem either. The issue was the parameter p_type should have been p_benefit_cd - a rename to due our conversion of our INGRES DBMS to SQLServer .... Also if there are multiple rows I want multiple rows inserted
– Colin Hay
Dec 21 '18 at 5:37