re SQL MSCRM Post
-
Hi we are having a really hard time determining the right
corse of action to take with our MS CRM project. Basically
we have a MS CRM system setup and fully functioning. The
problem that we face is that an outsourced party built
external SQL tables that record Interest and Benefit
information . When a specific delete is made in the CRM
system on this Benefit and Interest data the delete is
being made based on the BenefitTypeId. We need to find a
way to delete based on a unique id /index??
Here is all of the information that I think is needed
please let me know if I can provide additional information
to assit in the resolution of this problem.
sincerely Ryan Shawnego
I am trying to alter or if need be create a trigger that
will delete just one row out of a table. There are two
tables involved, one called Benefits and the other called
BenefitsType.
Benefits has 9 columns.
1. AccountId - Primary Key - Uniqueidentifier
2. BenefitTypeId - int - primary key
3. Unique_Id - int- primary key
4. BenefitsExtraContact - varchar
5. BenefitsComments - varchar
6. BenefitsDate - datetime
7. ContactId - uniqueidentifie
8. BenefitName - varchar
9. BenefitValue - money
The BenefitType has two columns.
1. BenefitTypeId - Primary key
2. BenefitDescription - varchar
There are 16 Benefit Types with a description on each. The
current delete trigger is in the benefittype table. What
happens when you select delete on the web page, it will
delete all benefit types of say 2. Would like it to just
delete one row of the benefit type number 2 instead of all
them. See trigger below.
create trigger tD_U_BenefitType on U_BenefitType for
DELETE as
/* MMC Builtin Sun Feb 25 11:12:06 2003 */
/* DELETE trigger on U_BenefitType */
begin
declare @errno int,
@errmsg varchar(255)
/* MMC Builtin Sun Feb 25 11:12:06 2003 */
/* U_BenefitType R/12 U_Benefits ON PARENT DELETE
RESTRICT */
if exists (
select * from deleted,U_Benefits
where
/* U_Benefits.BenefitTypeId =
deleted.BenefitTypeId */
U_Benefits.BenefitTypeId = deleted.BenefitTypeId
)
begin
select @errno = 30001,
@errmsg = 'Cannot DELETE U_BenefitType
because U_Benefits exists.'
goto error
end
/* MMC Builtin Sun Feb 25 11:12:06 2003 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
Would greatly appreciate any help on this.
-
Its not clear from the question.
What you want to do ?? When you delete the BENEFITTYPE from the web page, you want to delete all the BENEFITS associated with it ? or ??
Linto
-
I am assuming you just want to limit the amount of rows deleted in one transaction:
create trigger tD_U_BenefitType on U_BenefitType for
DELETE as
/* MMC Builtin Sun Feb 25 11:12:06 2003 */
/* DELETE trigger on U_BenefitType */
begin
declare @errno int,
@errmsg varchar(255)
/* MMC Builtin Sun Feb 25 11:12:06 2003 */
/* U_BenefitType R/12 U_Benefits ON PARENT DELETE
RESTRICT */
DECLARE @Cnt int
SET @Cnr = @@ROWCOUNT
IF @Cnt = 0
RETURN
If @Cnt >1
Begin
select @errno = 50002, @errmsg = 'Cannot DELETE More than one row at once'
goto error
End
if exists (
select * from deleted,U_Benefits
where
/* U_Benefits.BenefitTypeId =
deleted.BenefitTypeId */
U_Benefits.BenefitTypeId = deleted.BenefitTypeId
)
begin
select @errno = 30001,
@errmsg = 'Cannot DELETE U_BenefitType
because U_Benefits exists.'
goto error
end
/* MMC Builtin Sun Feb 25 11:12:06 2003 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
* Noel -
Yes, thank you Mr Linto, I am trying to limit the records deleted in one transaction. When I plug the added code that you sent me into the trigger I recieve the error must declar the variable @cnr.
sincerely,
Ryan Shawnego
-
You said... "Would like it to just delete one row of the benefit type number 2 instead of all them." If you mean that you want to delete one row from the Benefits table having a specific BenefitsType id, such as 2, then you don't need a trigger to do it ... just a DELETE that identifies the specific AccountId , Unique_Id, BenefitTypeId
... The defined trigger is on the BenefitsType table, and the BenefitTypeId is the primary key. Therefore there is only one record in the table with BenefitTypeId = 2, and trying to delete it will be impossible as long as there is a Foreign Key reference to it in Benefits.
-
Sorry if i misunderstood you..
First, the sample code put in the forum is not by me.. its by somebody else. To get rid of the error modify the line
SET @Cnr = @@ROWCOUNT to
SET @Cnt = @@ROWCOUNT
And if you want to delete all the benefits table entry when benefitstype deleted, you have to modify the trigger in the following way..
create trigger tD_U_BenefitType on U_BenefitType for
DELETE as
begin
declare @errno int,
@errmsg varchar(255)
if exists (
select * from deleted,U_Benefits
where
U_Benefits.BenefitTypeId = deleted.BenefitTypeId
)
begin
delete from U_Benefits where BenefitTypeId in (Select deleted.BenefitTypeId from deleted)
end
return
end
Please note, this will delete all the child table (BENEFITS) entries for the BenefitType.
Let me know you want something else!
Linto
-
sorry for the typo. I just tried to give you the Idea
* Noel
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply