Re: bedingte Beziehung

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Antje Kaiser wrote:
[Frage zu mehreren Spalten auf eine gemeinsame referenzieren]

Hallo Antje,
das Skript würde ungefähr so aussehen:

CREATE TABLE AW (
Feldname char(18) NOT NULL,
moeglicherWert integer NOT NULL
)
go
ALTER TABLE AW
ADD PRIMARY KEY CLUSTERED (Feldname ASC, moeglicherWert ASC)
go
CREATE TABLE X (
ID char(18) NOT NULL,
Rubrik char(18) NULL
)
go
ALTER TABLE X
ADD PRIMARY KEY CLUSTERED (ID ASC)
go
ALTER TABLE X
ADD FOREIGN KEY (Rubrik)
REFERENCES AW (Feldname, moeglicherWert)
ON DELETE NO ACTION
ON UPDATE NO ACTION
go


und folgenden Fehler liefern:
Number of referencing columns in foreign key differs from number of referenced columns, table 'X'.
Allgemeiner SQL Server-Fehler: Überprüfen Sie Meldungen vom Computer mit SQL Server.
Execution Failed!

Alternative: Du kannst so etwas über Trigger kontrollieren.

create trigger tD_AW on AW for DELETE as
/* ERwin Builtin Wed Mar 15 11:37:26 2006 */
/* DELETE trigger on AW */
begin
declare @errno int,
@errmsg varchar(255)
/* ERwin Builtin Wed Mar 15 11:37:26 2006 */
/* AW R/1 X ON PARENT DELETE NO ACTION */
/* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="AW"
CHILD_OWNER="", CHILD_TABLE="X"
P2C_VERB_PHRASE="R/1", C2P_VERB_PHRASE="",
FK_CONSTRAINT="R_1", FK_COLUMNS="Rubrik" */
if exists (
select * from deleted,X
where
/* %JoinFKPK(X,deleted," = "," and") */
X.Rubrik = deleted.Feldname and
X.Rubrik = deleted.moeglicherWert
)
begin
select @errno = 30001,
@errmsg = 'Cannot DELETE AW because X exists.'
goto error
end


/* ERwin Builtin Wed Mar 15 11:37:26 2006 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go

create trigger tU_AW on AW for UPDATE as
/* ERwin Builtin Wed Mar 15 11:37:26 2006 */
/* UPDATE trigger on AW */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@insFeldname char(18),
@insmoeglicherWert integer,
@errno int,
@errmsg varchar(255)

select @numrows = @@rowcount
/* ERwin Builtin Wed Mar 15 11:37:26 2006 */
/* AW R/1 X ON PARENT UPDATE NO ACTION */
/* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="AW"
CHILD_OWNER="", CHILD_TABLE="X"
P2C_VERB_PHRASE="R/1", C2P_VERB_PHRASE="",
FK_CONSTRAINT="R_1", FK_COLUMNS="Rubrik" */
if
/* %ParentPK(" or",update) */
update(Feldname) or
update(moeglicherWert)
begin
if exists (
select * from deleted,X
where
/* %JoinFKPK(X,deleted," = "," and") */
X.Rubrik = deleted.Feldname and
X.Rubrik = deleted.moeglicherWert
)
begin
select @errno = 30005,
@errmsg = 'Cannot UPDATE AW because X exists.'
goto error
end
end


/* ERwin Builtin Wed Mar 15 11:37:26 2006 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go

create trigger tD_X on X for DELETE as
/* ERwin Builtin Wed Mar 15 11:37:26 2006 */
/* DELETE trigger on X */
begin
declare @errno int,
@errmsg varchar(255)
/* ERwin Builtin Wed Mar 15 11:37:26 2006 */
/* AW R/1 X ON CHILD DELETE NO ACTION */
/* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="AW"
CHILD_OWNER="", CHILD_TABLE="X"
P2C_VERB_PHRASE="R/1", C2P_VERB_PHRASE="",
FK_CONSTRAINT="R_1", FK_COLUMNS="Rubrik" */
if exists (select * from deleted,AW
where
/* %JoinFKPK(deleted,AW," = "," and") */
deleted.Rubrik = AW.Feldname and
deleted.Rubrik = AW.moeglicherWert and
not exists (
select * from X
where
/* %JoinFKPK(X,AW," = "," and") */
X.Rubrik = AW.Feldname and
X.Rubrik = AW.moeglicherWert
)
)
begin
select @errno = 30010,
@errmsg = 'Cannot DELETE last X because AW exists.'
goto error
end


/* ERwin Builtin Wed Mar 15 11:37:26 2006 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go

create trigger tU_X on X for UPDATE as
/* ERwin Builtin Wed Mar 15 11:37:26 2006 */
/* UPDATE trigger on X */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@insID char(18),
@errno int,
@errmsg varchar(255)

select @numrows = @@rowcount
/* ERwin Builtin Wed Mar 15 11:37:26 2006 */
/* AW R/1 X ON CHILD UPDATE NO ACTION */
/* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="AW"
CHILD_OWNER="", CHILD_TABLE="X"
P2C_VERB_PHRASE="R/1", C2P_VERB_PHRASE="",
FK_CONSTRAINT="R_1", FK_COLUMNS="Rubrik" */
if
/* %ChildFK(" or",update) */
update(Rubrik) or
update(Rubrik)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,AW
where
/* %JoinFKPK(inserted,AW) */
inserted.Rubrik = AW.Feldname and
inserted.Rubrik = AW.moeglicherWert
/* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted where"," and") */
select @nullcnt = count(*) from inserted where
inserted.Rubrik is null and
inserted.Rubrik is null
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30007,
@errmsg = 'Cannot UPDATE X because AW does not exist.'
goto error
end
end


/* ERwin Builtin Wed Mar 15 11:37:26 2006 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go


Wie Du den Skripten entnehmen kannst, habe ich sie mit AllFusion ERwin DataModeler von CA erstellt.


Einen schönen Tag noch,
Christoph
--
(Please post ALL replies to the newsgroup only unless indicated otherwise)

.

Relevant Pages