adding only one instance in the table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

adding only one instance in the table

Author

Topic

shemayb
Posting Yak Master

159 Posts

Posted - 2008-04-02 : 12:35:01

The situation is like this,in my Requirements table,my fields are requirement_name,req_id and allow_multiple.allow_multiple.The fiels allow_multiple has a value of 0 and 1.In the Staff table,the fields are staff_id,staff_name,req_id.When we add requirement name to the staff table ot should check for the allow_multiple field, if it is 0 i can only add 1 instance of that requirement to the staff and if it is 1 i can add many instances of that requirement to the staff.How can i do that?

thank you..

Funnyfrog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-02 : 12:39:46

When do you want to do this population of staff table? EVery time when you add a record to a requirement table?

shemayb
Posting Yak Master

159 Posts

Posted - 2008-04-02 : 12:44:15

When i add a record to the staff table,it should check whether the requirement to be added allows mutiple addtion or not.My code behind for this is c#.

Funnyfrog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-02 : 12:58:48

You can write an INSTEAD OF TRIGGER which checks for the value of allow multiple field before insertion and checks if it is first or subsequent insert for the req_id and insert accordingly.

i.e something like

CREATE TRIGGER Staff_I on Staff
INSTEAD OF INSERT
AS
BEGIN

IF (SELECT COUNT(*) FROM INSERTED i
    INNER JOIN Requirements r
    ON r.req_id = i.req_id)=0--first insertion
OR ((SELECT COUNT(*) FROM INSERTED i
    INNER JOIN Requirements r
    ON r.req_id = i.req_id)> 0 AND 
   (SELECT r.allow_multiple FROM INSERTED i
    INNER JOIN Requirements r
    ON r.req_id = i.req_id)=1)--subsequent insertion but allow_multiple=1
BEGIN
INSERT INTO Staff
SELECT * FROM INSERTED
END
END
GO

shemayb
Posting Yak Master

159 Posts

Posted - 2008-04-02 : 13:56:07

What do you mean by INSTEAD OF INSERT?

Funnyfrog

shemayb
Posting Yak Master

159 Posts

Posted - 2008-04-02 : 13:59:54

this is my stored procedure for that:

ALTER PROCEDURE [dbo].[insertStaff]
(@staff_id int,
@staff_name,
@req_id varchar(20))

)
AS

BEGIN TRAN

INSERT INTO dbo.Staff
(staff_id,staff_name,req_id)
VALUES
(
@staff_id,
@staff_name,
@req_id

)

IF @@ERROR <> 0
BEGIN
RAISERROR ('Failed to INSERT value into dbo.Staff', 16, 1)
ROLLBACK TRANSACTION
END

COMMIT TRAN

Funnyfrog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-02 : 14:13:57

quote:
Originally posted by shemayb

What do you mean by INSTEAD OF INSERT?

Funnyfrog

its a type of trigger which performs actions (checks) before insert action is about to happen on table on which its created. And here you need to check the allow_multiple value before insertion and insert accordingly.

shemayb
Posting Yak Master

159 Posts

Posted - 2008-04-02 : 14:27:54

I have inserted your sample code in my insertStaff stored procedure.Am i on the right track?

ALTER PROCEDURE [dbo].[insertStaff]
(@staff_id int,
@staff_name,
@req_id varchar(20))

)
AS
CREATE TRIGGER Staff_I on Staff
INSTEAD OF INSERT
AS
BEGIN

IF (SELECT COUNT(*) FROM INSERTED i
INNER JOIN Requirements r
ON r.req_id = i.req_id)=0
OR ((SELECT COUNT(*) FROM INSERTED i
INNER JOIN Requirements r
ON r.req_id = i.req_id)> 0 AND
(SELECT r.allow_multiple FROM INSERTED i
INNER JOIN Requirements r
ON r.req_id = i.req_id)=1)

BEGIN TRAN

INSERT INTO dbo.Staff
(staff_id,staff_name,req_id)
VALUES
(
@staff_id,
@staff_name,
@req_id

)
SELECT * FROM INSERTED

IF @@ERROR <> 0
BEGIN
RAISERROR ('Failed to INSERT value into dbo.Staff', 16, 1)
ROLLBACK TRANSACTION
END

COMMIT TRAN

Funnyfrog

X002548
Not Just a Number

15586 Posts

Posted - 2008-04-02 : 14:34:03

quote:
Originally posted by shemayb

Am i on the right track?

no

Why not sure some sample data, and what the expected results are suppose to be

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-02 : 14:34:08

quote:
Originally posted by shemayb

I have inserted your sample code in my insertStaff stored procedure.Am i on the right track?

ALTER PROCEDURE [dbo].[insertStaff]
(@staff_id int,
@staff_name,
@req_id varchar(20))

)
AS
CREATE TRIGGER Staff_I on Staff
INSTEAD OF INSERT
AS
BEGIN

IF (SELECT COUNT(*) FROM INSERTED i
INNER JOIN Requirements r
ON r.req_id = i.req_id)=0
OR ((SELECT COUNT(*) FROM INSERTED i
INNER JOIN Requirements r
ON r.req_id = i.req_id)> 0 AND
(SELECT r.allow_multiple FROM INSERTED i
INNER JOIN Requirements r
ON r.req_id = i.req_id)=1)

BEGIN TRAN

INSERT INTO dbo.Staff
(staff_id,staff_name,req_id)
VALUES
(
@staff_id,
@staff_name,
@req_id

)
SELECT * FROM INSERTED

IF @@ERROR <> 0
BEGIN
RAISERROR ('Failed to INSERT value into dbo.Staff', 16, 1)
ROLLBACK TRANSACTION
END

COMMIT TRAN

Funnyfrog

Nope just run my code alone first. It will create trigger on your Staff table. Then execute the procedure and trigger will check condition automatically for you and insert only if its first record when allow_multiple =0 and several records when its 1. You can also refer to INSTEAD OF TRIGGER on BOL for more details.

shemayb
Posting Yak Master

159 Posts

Posted - 2008-04-02 : 14:46:15

I will run it as a stored procedure? or i will just run it directly?

Funnyfrog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-02 : 14:47:48

It will run by itself once you execute this code on your db once. Each time you execute SP to insert, it gets invoked automatically.

shemayb
Posting Yak Master

159 Posts

Posted - 2008-04-02 : 14:50:25

Is INSERTED a temporary table?

Funnyfrog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-02 : 15:02:11

Yup. its is a temporary table which is available to trigger. This contains the values you try to insert into table currently.

shemayb
Posting Yak Master

159 Posts

Posted - 2008-04-02 : 15:04:51

okay..I will try it now..thank you so much!

Funnyfrog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-02 : 15:06:32

You are welcome

Make sure you read and understand about INSTEAD OF TRIGGERS from BOL

shemayb
Posting Yak Master

159 Posts

Posted - 2008-04-02 : 15:09:47

When i run the code,i got this error message:
An explicit value for the identity column in table 'Staff' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Funnyfrog

shemayb
Posting Yak Master

159 Posts

Posted - 2008-04-02 : 19:10:40

How can i remove the trigger? I wrote a wrong id name..

Funnyfrog

shemayb
Posting Yak Master

159 Posts

Posted - 2008-04-02 : 19:36:40

Do i need to put in values in my INSERT INTO Staff statement?

Funnyfrog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-03 : 01:50:53