CREATE TABLE [dbo].[tCompy](
        [idx] [smallint] IDENTITY(1,1) NOT NULL,
        [cd] [varchar](50) NOT NULL,
        [desn] [varchar](50) NOT NULL,
        [stat] [bit] NOT NULL,
        [crtby] [varchar](20) NOT NULL,
        [crtdte] [smalldatetime] NOT NULL CONSTRAINT [DF_tCompany_crtdte]  DEFAULT (getdate()),
        [updby] [varchar](20) NOT NULL CONSTRAINT [DF_tCompany_updby]  DEFAULT ('na'),
        [upddte] [smalldatetime] NOT NULL CONSTRAINT [DF_tCompany_upddte]  DEFAULT (getdate()),
        [editno] [smallint] NOT NULL CONSTRAINT [DF_tCompany_editno]  DEFAULT ((1)),
 CONSTRAINT [PK_tCompany] PRIMARY KEY CLUSTERED 
(
        [idx] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [tcompany01] UNIQUE NONCLUSTERED 
(
        [cd] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY];
insert into tCompy(cd,desn,stat,crtby)
values('snt','sn tech',1,'admin');

CREATE TABLE [dbo].[tCout](
        [idx] [smallint] IDENTITY(1,1) NOT NULL,
        [cd] [varchar](20) NOT NULL,
        [desn] [varchar](50) NOT NULL,
        [inettrnx] [bit] NOT NULL,
        [stat] [bit] NOT NULL,
        [remk] [varchar](100) NOT NULL,
        [crtby] [varchar](20) NOT NULL,
        [crtdte] [smalldatetime] NOT NULL CONSTRAINT [DF_TCounter_crtdte]  DEFAULT (getdate()),
        [updby] [varchar](20) NOT NULL CONSTRAINT [DF_tCounter_updby]  DEFAULT ('na'),
        [upddte] [smalldatetime] NOT NULL CONSTRAINT [DF_tCounter_upddte]  DEFAULT (getdate()),
        [editno] [smallint] NOT NULL CONSTRAINT [DF_tCounter_editno]  DEFAULT ((1)),
 CONSTRAINT [PK_tCounter] PRIMARY KEY CLUSTERED 
(
        [idx] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY],
 CONSTRAINT [tcounter01] UNIQUE NONCLUSTERED 
(
        [cd] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY];
insert into tCout
(cd, desn, inettrnx, stat, remk, crtby)
values
('kl','kuala lumpur',1,1,'','admin');
insert into tCout
(cd, desn, inettrnx, stat, remk, crtby)
values
('iph','ipoh',1,1,'','admin');

CREATE TABLE [dbo].[t1](
        [idx] [smallint] IDENTITY(1,1) NOT NULL,
        [tcompycd] [varchar](50) NOT NULL,
 CONSTRAINT [PK_t1] PRIMARY KEY CLUSTERED 
(
        [idx] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[t1]  WITH CHECK ADD  CONSTRAINT [FK_t1_tcompycd] FOREIGN KEY([tcompycd])
REFERENCES [dbo].[tCompy] ([cd])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[t1] CHECK CONSTRAINT [FK_t1_tcompycd];

CREATE TABLE [dbo].[t2](
        [idx] [smallint] IDENTITY(1,1) NOT NULL,
        [t1idx] [smallint] NOT NULL,
        [tcoutcd] [varchar](20) NOT NULL,
 CONSTRAINT [PK_t2] PRIMARY KEY CLUSTERED 
(
        [idx] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[t2]  WITH CHECK ADD  CONSTRAINT [FK_t2_t1idx] FOREIGN KEY([t1idx])
REFERENCES [dbo].[t1] ([idx])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[t2] CHECK CONSTRAINT [FK_t2_t1idx]
GO
ALTER TABLE [dbo].[t2]  WITH CHECK ADD  CONSTRAINT [FK_t2_tcoutcd] FOREIGN KEY([tcoutcd])
REFERENCES [dbo].[tCout] ([cd])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[t2] CHECK CONSTRAINT [FK_t2_tcoutcd];

CREATE TABLE [dbo].[t3](
        [idx] [smallint] IDENTITY(1,1) NOT NULL,
        [t1idx] [smallint] NOT NULL,
        [tcoutcd] [varchar](20) NOT NULL,
 CONSTRAINT [PK_t3] PRIMARY KEY CLUSTERED 
(
        [idx] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[t3]  WITH CHECK ADD  CONSTRAINT [FK_t3_t1idx] FOREIGN KEY([t1idx])
REFERENCES [dbo].[t1] ([idx])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[t3] CHECK CONSTRAINT [FK_t3_t1idx]
GO
ALTER TABLE [dbo].[t3]  WITH CHECK ADD  CONSTRAINT [FK_t3_tcoutcd] FOREIGN KEY([tcoutcd])
REFERENCES [dbo].[tCout] ([cd])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[t3] CHECK CONSTRAINT [FK_t3_tcoutcd]

declare @idx smallint
insert into t1 values('snt');
set @idx=scope_identity();
insert into t2(t1idx,tcoutcd) values(@idx,'iph');

quote:

---

Originally posted by tkizer

A constraint can not span more than one table. Hope I understood your question correctly.

You can instead write a trigger.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

---

declare @idx smallint
insert into t1 values('snt');
set @idx=scope_identity();
insert into t2(t1idx,tcoutcd) values(@idx,'iph');

CREATE TRIGGER Yourtrigger
ON t2
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON

IF EXISTS(SELECT t1.tcompycd, i.tcoutcd 
   FROM t1 
   JOIN INSERTED i
   ON i.t1idx =t1.idx
   GROUP BY  t1.tcompycd, i.tcoutcd
   HAVING COUNT(*)=1)
BEGIN
INSERT INTO t2
SELECT MAX(i.idx),i.t1idx,i.tcoutcd
FROM t1 
JOIN INSERTED i
ON i.t1idx =t1.idx
GROUP BY  i.t1idx,t1.tcompycd, i.tcoutcd
HAVING COUNT(*)=1
END
END

quote:

---

Originally posted by visakh16

do you mean something like

CREATE TRIGGER Yourtrigger
ON t2
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON

IF EXISTS(SELECT t1.tcompycd, i.tcoutcd 
   FROM t1 
   JOIN INSERTED i
   ON i.t1idx =t1.idx
   GROUP BY  t1.tcompycd, i.tcoutcd
   HAVING COUNT(*)=1)
BEGIN
INSERT INTO t2
SELECT MAX(i.idx),i.t1idx,i.tcoutcd
FROM t1 
JOIN INSERTED i
ON i.t1idx =t1.idx
GROUP BY  i.t1idx,t1.tcompycd, i.tcoutcd
HAVING COUNT(*)=1
END
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

CREATE TRIGGER t201 
   ON  t2 
   INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON

IF EXISTS(SELECT t1.tcompycd, i.tcoutcd 
   FROM t1 JOIN INSERTED i ON i.t1idx =t1.idx
   GROUP BY  t1.tcompycd, i.tcoutcd HAVING COUNT(*)=1)
	BEGIN
		INSERT INTO t2
		SELECT MAX(i.idx),i.t1idx,i.tcoutcd
		FROM t1 
		JOIN INSERTED i
		ON i.t1idx =t1.idx
		GROUP BY  i.t1idx,t1.tcompycd, i.tcoutcd
		HAVING COUNT(*)=1
	END
END

quote:

---

Originally posted by Idera

It means the value in column id in table t2 is set as an autoincrement i.e an identity property has been set for the column.

PBUH

---

quote:

---

Originally posted by Idera

Just run this
SET IDENTITY_INSERT t2 ON

PBUH

---

ALTER TRIGGER [dbo].[t201] 
   ON  [dbo].[t2] 
   INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
SET IDENTITY_INSERT t2 ON

IF EXISTS(SELECT t1.tcompycd, i.tcoutcd 
   FROM t1 JOIN INSERTED i ON i.t1idx =t1.idx
   GROUP BY  t1.tcompycd, i.tcoutcd HAVING COUNT(*)=1)
	BEGIN        
		INSERT INTO t2
		SELECT MAX(i.idx),i.t1idx,i.tcoutcd
		FROM t1 
		JOIN INSERTED i
		ON i.t1idx =t1.idx
		GROUP BY  i.t1idx,t1.tcompycd, i.tcoutcd
		HAVING COUNT(*)=1
	END
END

quote:

---

Originally posted by Idera

Did u try my solution.Please run this

SET IDENTITY_INSERT t2 ON

I cannot be more specific than this.

PBUH

---

SET IDENTITY_INSERT t2 ON

ALTER TRIGGER [dbo].[t201] 
   ON  [dbo].[t2] 
   INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON

IF EXISTS(SELECT t1.tcompycd, i.tcoutcd 
   FROM t1 JOIN INSERTED i ON i.t1idx =t1.idx
   GROUP BY  t1.tcompycd, i.tcoutcd HAVING COUNT(*)=1)
	BEGIN        
		INSERT INTO t2
		SELECT MAX(i.idx),i.t1idx,i.tcoutcd
		FROM t1 
		JOIN INSERTED i
		ON i.t1idx =t1.idx
		GROUP BY  i.t1idx,t1.tcompycd, i.tcoutcd
		HAVING COUNT(*)=1
	END
END

quote:

---

Originally posted by GilaMonster

The identity insert goes inside the trigger. Not run separately.

--
Gail Shaw
SQL Server MVP

---

ALTER TRIGGER [dbo].[t201] 
   ON  [dbo].[t2] 
   INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
SET IDENTITY_INSERT t2 ON

IF EXISTS(SELECT t1.tcompycd, i.tcoutcd 
   FROM t1 JOIN INSERTED i ON i.t1idx =t1.idx
   GROUP BY  t1.tcompycd, i.tcoutcd HAVING COUNT(*)=1)
	BEGIN        
		INSERT INTO t2
		SELECT MAX(i.idx),i.t1idx,i.tcoutcd
		FROM t1 
		JOIN INSERTED i
		ON i.t1idx =t1.idx
		GROUP BY  i.t1idx,t1.tcompycd, i.tcoutcd
		HAVING COUNT(*)=1
	END
END

INSERT INTO t2 (t1idx ,tcoutcd)
		SELECT i.t1idx,i.tcoutcd
		FROM t1 
		JOIN INSERTED i
		ON i.t1idx =t1.idx
		GROUP BY  i.t1idx,t1.tcompycd, i.tcoutcd
		HAVING COUNT(*)=1

quote:

---

Originally posted by visakh16

make last insert like below and try

INSERT INTO t2 (t1idx ,tcoutcd)
		SELECT i.t1idx,i.tcoutcd
		FROM t1 
		JOIN INSERTED i
		ON i.t1idx =t1.idx
		GROUP BY  i.t1idx,t1.tcompycd, i.tcoutcd
		HAVING COUNT(*)=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

CREATE TRIGGER [dbo].[t201]
   ON  [dbo].[t2]
   INSTEAD OF INSERT
AS 
BEGIN
SET NOCOUNT ON

IF EXISTS(SELECT t1.tcompycd, i.tcoutcd 
   FROM t1 JOIN INSERTED i ON i.t1idx =t1.idx
   GROUP BY  t1.tcompycd, i.tcoutcd HAVING COUNT(*)=1)
	BEGIN        
		INSERT INTO t2 (t1idx ,tcoutcd)
		SELECT i.t1idx,i.tcoutcd
		FROM t1 
		JOIN INSERTED i
		ON i.t1idx =t1.idx
		GROUP BY  i.t1idx,t1.tcompycd, i.tcoutcd
		HAVING COUNT(*)=1
	END
END

declare @idx smallint
insert into t1 values('snt');
set @idx=scope_identity();
insert into t2(t1idx,tcoutcd) values(@idx,'iph');