Parent Table Control

Hi All,
I Have 2 tables, and when I DELETE or UPDATE 'TPARENT' the 'CLILD' also
UPDATED, DELETED.
If I DELETE in CLILD, the row in CLILD is DELETED, but in 'TPARENT' no.
I would like know if have way to I specifique that DELETE can be used only
in 'TPARENT'
If user try use DELETE in CLILD he receive a error, user can use DELETE
only in TPARENT.
I did try use triger, but if I try DELET of TPARENT I also receive the
error, I want receive erro on;y if I try use DELETE on CLILD
IF EXISTS(SELECT NAME
FROM sysobjects
WHERE NAME = 'BlockDeleteOnDomiciliosBancarios'
AND type = 'TR')
DROP TRIGGER BlockDeleteOnDomiciliosBancarios
GO
CREATE TRIGGER BlockDeleteOnDomiciliosBancarios
ON DomiciliosBancarios
FOR DELETE
AS
BEGIN
ROLLBACK TRANSACTION
PRINT ('No possvel apagar de DomiciliosBancarios')
END
can anyone help-me -- Thanks
CREATE TABLE TPARENT
(
CONSTRAINT pk_TPARENT
PRIMARY KEY(TPARENT),
TPARENT CHAR(30)
NOT NULL
)
INSERT INTO TPAI VALUES ('Test 01')
INSERT INTO TPAI VALUES ('Test 02')
INSERT INTO TPAI VALUES ('Test 03')
---
CREATE TABLE CLILD
(
CONSTRAINT fk_CLILD
FOREIGN KEY(TPARENT )
References TPARENT (TPARENT )
ON UPDATE CASCADE
ON DELETE CASCADE,
TPARENT CHAR(30)
NOT NULL
)Hi,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to know how to delete
rows in TPARENT table. However, I am not sure what's the exact error
message when "If user try use DELETE in CLILD he receive a error, user can
use DELETE only in TPARENT." Would you please help describe it further? If
I have misunderstood your concern, please feel free to point it out.
Based on my knowlegde, when you are specifing ON DELETE/UPDATE CASCADE,
CLILD table's related rows will also be deleted when you are deleting
TPARENT rows.
Since you have specificed FOREIGN KEY, you cannot delete rows in TPARENT
while leave the rows in CLILD. The related rows in CLILD will be also
deleted. If you want to delete rows in TPARENT and not delete rows in
CLILD, you must specify your won trigger to accomplish this instead of
using FOREIGN KEY.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Here is a suggestion: Use both a hidden table and a public view for
CLILD. Put the referential integrity on the hidden table, and put
an INSTEAD OF trigger on the view that generates an error and
does not delete anything. If you need to, roll back the transaction
in the trigger also, but since it is an INSTEAD OF trigger, there is
no DELETE to roll back. Here is a repro:
CREATE TABLE TPARENT
(
CONSTRAINT pk_TPARENT
PRIMARY KEY(TPARENT),
TPARENT CHAR(30)
NOT NULL
)
INSERT INTO TPARENT VALUES ('Test 01')
INSERT INTO TPARENT VALUES ('Test 02')
INSERT INTO TPARENT VALUES ('Test 03')
-- Hidden table with foreign key constraint
CREATE TABLE CLILD_hidden
(
CONSTRAINT fk_CLILD
FOREIGN KEY(TPARENT )
References TPARENT (TPARENT )
ON UPDATE CASCADE
ON DELETE CASCADE,
TPARENT CHAR(30)
NOT NULL
)
go
-- view that is used externally as the table
create view CLILD as
select TPARENT from CLILD_hidden
go
insert into CLILD VALUES ('Test 01')
insert into CLILD VALUES ('Test 02')
insert into CLILD VALUES ('Test 03')
go
-- do not allow delete from the view
CREATE TRIGGER BlockDeleteOnCLILD
ON CLILD INSTEAD OF DELETE
AS
-- ROLLBACK TRANSACTION -- if necessary for other reasons
PRINT ('No possvel apagar de CLILD')
go
select * from CLILD
go
delete from TPARENT
where TPARENT = 'Test 01'
go
select * from CLILD
go
delete from CLILD
where TPARENT = 'Test 02'
go
delete from TPARENT
where TPARENT = 'Test 03'
go
select * from CLILD
go
-- drop view CLILD
-- drop table CLILD_hidden, TPARENT
-- Steve Kass
-- Drew University
ReTF wrote:

>Hi All,
>I Have 2 tables, and when I DELETE or UPDATE 'TPARENT' the 'CLILD' also
>UPDATED, DELETED.
>If I DELETE in CLILD, the row in CLILD is DELETED, but in 'TPARENT' no.
>I would like know if have way to I specifique that DELETE can be used only
>in 'TPARENT'
>If user try use DELETE in CLILD he receive a error, user can use DELETE
>only in TPARENT.
>I did try use triger, but if I try DELET of TPARENT I also receive the
>error, I want receive erro on;y if I try use DELETE on CLILD
>IF EXISTS(SELECT NAME
> FROM sysobjects
> WHERE NAME = 'BlockDeleteOnDomiciliosBancarios'
> AND type = 'TR')
> DROP TRIGGER BlockDeleteOnDomiciliosBancarios
>GO
>CREATE TRIGGER BlockDeleteOnDomiciliosBancarios
>ON DomiciliosBancarios
>FOR DELETE
>AS
>BEGIN
> ROLLBACK TRANSACTION
> PRINT ('No possvel apagar de DomiciliosBancarios')
>END
>can anyone help-me -- Thanks
>CREATE TABLE TPARENT
>(
> CONSTRAINT pk_TPARENT
> PRIMARY KEY(TPARENT),
> TPARENT CHAR(30)
> NOT NULL
> )
>INSERT INTO TPAI VALUES ('Test 01')
>INSERT INTO TPAI VALUES ('Test 02')
>INSERT INTO TPAI VALUES ('Test 03')
>---
>CREATE TABLE CLILD
>(
> CONSTRAINT fk_CLILD
> FOREIGN KEY(TPARENT )
> References TPARENT (TPARENT )
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> TPARENT CHAR(30)
> NOT NULL
> )
>
>|||Hi,
I want block if the user try DELETE of CLILD, the user can DELETE only of
TPARENT .
Because CLILD has ON DELETE/UPDATE CASCADE, when TPARENT (row) is deleted in
CLILD this also deleted.
But if user try use DELETE direct in CLILD the user must receive a error,
the user can only use DELETE in TPARENT no in childs.
sorry about my english, this is not my native language, if you don't
understand let-me know and I will explain again. Thanks
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> escreveu na mensagem
news:azlw3bxlFHA.3672@.TK2MSFTNGXA01.phx.gbl...
> Hi,
> Welcome to use MSDN Managed Newsgroup!
> From your descriptions, I understood you would like to know how to delete
> rows in TPARENT table. However, I am not sure what's the exact error
> message when "If user try use DELETE in CLILD he receive a error, user
> can
> use DELETE only in TPARENT." Would you please help describe it further? If
> I have misunderstood your concern, please feel free to point it out.
> Based on my knowlegde, when you are specifing ON DELETE/UPDATE CASCADE,
> CLILD table's related rows will also be deleted when you are deleting
> TPARENT rows.
> Since you have specificed FOREIGN KEY, you cannot delete rows in TPARENT
> while leave the rows in CLILD. The related rows in CLILD will be also
> deleted. If you want to delete rows in TPARENT and not delete rows in
> CLILD, you must specify your won trigger to accomplish this instead of
> using FOREIGN KEY.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi,
Thanks for your reply.
I understood you request as below
1) User is not able to delete from CLILD table
2) User could delete from TPARENT table
3) When rows in TPARENT table is deleted, remain the related rows in CLILD
table.
If I have misunderstood your concern, please feel free to point it out.
To accomplish this, you cannot use Foreign Key in your tables. As I have
said before, you'd better create your own triggers to do so.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.