want to insert a single row in a procedure

Question

0.00/5 (No votes)

See more:

hai,

i have a table Queue_Number having columns ID , Queue_Type , Queue_Status , Reference_ID , QID , QNo , QID_No

i have written trigger for insertion in this QID , QNo , QID_No will be inserted automatically by the trigger which is shown below , i want to insert the remaining columns

Queue_Type , Reference_ID externally.

i am inserting Queue_Type , Reference_ID externally for that i am getting result as the two columns Queue_Type , Reference_ID are inserting in one row and the remaining
QID , QNo , QID_No inserting in another row.

SQL

CREATE TRIGGER QNO_GENERATION
ON Queue_Number
FOR INSERT
AS
BEGIN

DECLARE @QID_No NVarchar(25) = NULL
DECLARE @QId NVarchar(6) = NULL
DECLARE @QNo Int = 0
DECLARE @Month Int = 0
DECLARE @Queue_Type INT
DECLARE @Reference_ID INT


SELECT @QId=I.QID FROM INSERTED I
SELECT @QNo=I.QNo FROM INSERTED I
SELECT @QID_No=I.QID_No FROM INSERTED I
SELECT @Reference_ID=I.Reference_ID FROM INSERTED I


SET @Month = (SELECT DATEPART(MONTH,GETDATE()))

IF @Month > 3
BEGIN
    SET @QId = (SELECT (CONVERT(NVARCHAR(4),DATEPART(Year,GETDATE())) + CONVERT(NVarchar(2),RIGHT((DATEPART(Year,DATEADD(Year,1,GETDATE()))),2))))
END

SET @QNo = (SELECT QNo FROM Queue_Number WHERE QID_No = @QId)

SET @QNo = @QNo + 1

SET @QID_No = @QId + (SELECT (CONVERT(NVarchar(10),@QNo)))

INSERT INTO Queue_Number (QID,QNo,QID_No,Queue_Type,Reference_ID) VALUES (@QId,@QNo,@QID_No,@Queue_Type,@Reference_ID)

SELECT @Reference_ID AS QueueNo

END

Posted 24-Jul-12 20:29pm

sk. maqdoom ali

Updated 24-Jul-12 21:57pm

Sandeep Mewara

v2

Add a Solution

Comments

graciax8 25-Jul-12 2:36am

what is your question?

sk. maqdoom ali 25-Jul-12 4:17am

i want to insert 5 fields into the table , in that the three fields QID,QNo,QID_No are automatically inserted when i insert another another two fields ie, Queue_Type, Reference_ID .
when i am inserting these two field values externally ie, insert into table(Queue_Type, Reference_ID)values(1,12)
after doing this the insertion the inserted values in the table as
for Queue_Type, Reference_ID the values are inserted in one row but the remaining fields QID,QNo,QID_No are inserted in another row.

i want to insert all the fields in a single row only

graciax8 25-Jul-12 5:12am

With all the parameters and declarations you wrote here. I suggest you need to create a stored procedure not a trigger.

sk. maqdoom ali 25-Jul-12 5:14am

i want after insertion of Queue_Type, Reference_ID fields the trigger must be fire and insert the computed date into QID,QNo,QID_No fields

graciax8 25-Jul-12 5:23am

get the id of the insertion of Queue_Type, Reference_ID.
get the result and insert into QID,QNo,QID_No fields. For me you can easily do that using stored proc. Goodluck!

sk. maqdoom ali 25-Jul-12 5:25am

i am externally inserting these two values Queue_Type, Reference_ID

graciax8 25-Jul-12 21:16pm

same time? post your codes.

Sandeep Mewara 25-Jul-12 3:57am

Not clear. Where are you stuck?

sk. maqdoom ali 25-Jul-12 4:17am

i want to insert 5 fields into the table , in that the three fields QID,QNo,QID_No are automatically inserted when i insert another another two fields ie, Queue_Type, Reference_ID .
when i am inserting these two field values externally ie, insert into table(Queue_Type, Reference_ID)values(1,12)
after doing this the insertion the inserted values in the table as
for Queue_Type, Reference_ID the values are inserted in one row but the remaining fields QID,QNo,QID_No are inserted in another row.

i want to insert all the fields in a single row only

Dougie the P 2-Aug-12 20:28pm

You need to use an UPDATE statment not a INSERT statment in your trigger to update the QID,QNo,QID_No columns in the inserted record rather then insert a new row.

sk. maqdoom ali 3-Aug-12 0:19am

yes , i updated it

Dougie the P 3-Aug-12 1:04am

Please repost your current code reflecting the changes you have made and what your contiuing problems are.

sk. maqdoom ali 4-Aug-12 0:36am

ALTER TRIGGER Qno_Generation
ON Queue_Number
AFTER INSERT
AS
BEGIN

DECLARE @ID INT
DECLARE @QID_No NVarchar(25)
DECLARE @QId NVarchar(6)
DECLARE @QNo Int = 0
DECLARE @Month Int = 0

SELECT @ID=I.ID FROM INSERTED I
SELECT @QId=I.QID FROM INSERTED I
SELECT @QNo=I.QNo FROM INSERTED I
SELECT @QID_No=I.QID_No FROM INSERTED I

SET @Month = (SELECT DATEPART(MONTH,GETDATE()))

IF @Month > 3
BEGIN
SET @QId = (SELECT (CONVERT(NVARCHAR(4),DATEPART(Year,GETDATE())) + CONVERT(NVarchar(2),RIGHT((DATEPART(Year,DATEADD(Year,1,GETDATE()))),2))))
END

SET @QNo = (SELECT count(QNo) FROM Queue_Number WHERE QID = @QID)

SET @QNo = @QNo + 1

SET @QID_No = @QId + (SELECT (CONVERT(NVarchar(10),@QNo)))

update Queue_Number set QID=@QID,
QNo=@QNo,
QID_No=@QID_No

WHERE ID=@ID

END

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)