May 20, 2015 at 6:30 am
Hi,
I have a problem working with below mentioned situation. Please help me out with this.
I have table- Table1 with ID- Primary key, and many other columns.
Every time when user insert, records I have to generate ID (running number across tables in DB) and add it to Table1 with all other values and also update the name of Table (i.e Table1) and ID (inserted ID) into other table Table2. Also I want to trigger this for each row when user is inserting multiple values at a time (For this I tried to use cursor). This should trigger based on howmany times user is inserting values.
For this I have create a trigger Trigger 1 (to generate ID) from there I am calling stored procedure 'PRDC' to store the table name and ID into Table 2.
Problem I am facing.
1. The ID it is storing in Table1 and Table2 are not same.
2. Procedure is executing multiple times.
My code is as follows.
Trigger1:
ALTER TRIGGER [dbo].[TRG_ME_ADAPTER_CU] ON [dbo].[ME_ADAPTER_CU] AFTER INSERT, UPDATE, DELETE
AS
declare
@P_EVENT varchar(max),
@P_NEW_ID float(53),
@P_N_CRTDATE datetime2(0),
@P_TNAME varchar(max),
@P_NAME varchar(max)
BEGIN
declare cur_row CURSOR Local
STATIC FOR
select @P_NEW_ID from Table1
OPEN cur_row
IF @@CURSOR_ROWS > 0
BEGIN
FETCH NEXT FROM cur_row into @P_NEW_ID
IF NOT EXISTS (SELECT * FROM deleted)
WHILE @@Fetch_status = 0
BEGIN
select @P_EVENT='I', @P_N_CRTDATE=CREATED_DATE,@P_TNAME='Table1',@P_NAME= Name from inserted
EXEC PRDC @P_EVENT,@P_NEW_ID,@P_N_CRTDATE,@P_TNAME,@P_NAME
FETCH NEXT FROM cur_row into @P_NEW_ID
end
END
Procedure:
ALTER PROCEDURE PRDC
@P_EVENT varchar(max),
@P_NEW_ID float(53) output,
@P_N_CRTDATE datetime2(0),
@P_TNAME varchar(max),
@P_NAME varchar(max)
AS
BEGIN
IF @P_EVENT = 'I'
BEGIN
SET @P_NEW_ID = NEXT VALUE FOR dbo.SEQ_MASTER----------GENERATE NEW ID---------------
INSERT Table2(TABLENAME, ID, NAME)
VALUES (@P_TNAME, @P_NEW_ID, @P_NAME)
END
END
May 20, 2015 at 7:58 am
Hi and welcome to the forums. Your trigger doesn't make much sense to me. You have specified it to be a trigger for insert, update and delete. However, it doesn't do anything if there are no rows in deleted so why bother making it an insert trigger?
You also have a logic flaw in there. You set a scalar variable with a select statement but there is no where clause. This means it will receive the value from the last row in the query. There is no order by so you don't know which row it will be.
Here is your trigger code formatted (there are some parsing errors in here):
ALTER TRIGGER [dbo].[TRG_ME_ADAPTER_CU] ON [dbo].[ME_ADAPTER_CU]
AFTER INSERT
,UPDATE
,DELETE
AS
DECLARE @P_EVENT VARCHAR(max)
,@P_NEW_ID FLOAT(53)
,@P_N_CRTDATE DATETIME2(0)
,@P_TNAME VARCHAR(max)
,@P_NAME VARCHAR(max)
BEGIN
DECLARE cur_row CURSOR LOCAL STATIC
FOR
SELECT @P_NEW_ID
FROM Table1
OPEN cur_row
IF @@CURSOR_ROWS > 0
BEGIN
FETCH NEXT
FROM cur_row
INTO @P_NEW_ID
IF NOT EXISTS (
SELECT *
FROM deleted
)
WHILE @@Fetch_status = 0
BEGIN
SELECT @P_EVENT = 'I'
,@P_N_CRTDATE = CREATED_DATE
,@P_TNAME = 'Table1'
,@P_NAME = NAME
FROM inserted
EXEC PRDC @P_EVENT
,@P_NEW_ID
,@P_N_CRTDATE
,@P_TNAME
,@P_NAME
FETCH NEXT
FROM cur_row
INTO @P_NEW_ID
END
END
Now your procedure, why float(53)? Why bother passing in the new id variable just to throw away the value sent in and replace it? Why not just eliminate the parameter.
Overall this entire process seems to stuck in RBAR mode when it could be a single insert statement. I would be happy to help but you need to provide some details first. Please take a few minutes and read the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 21, 2015 at 4:16 am
Hi,
Thanks for your quick reply. I am new to SQL server infact RDBMS:-). So there will be lot of mistakes on my code.
I need help in understanding and making this trigger and sequences.
Let me put My requirement again here:
I have table - Table1 (with ID as PK)
One more table - Table2 with 3 colums (ID, table name, name)
When user inserts/deletes/updates a rows into Table1, i have to generate a ID (using squence genrator) and update records into Table1 and also with the same ids I have to add it to Table2.
For this I am writing Trigger on Table1 and generating ID (using sequence generator) after this I am calling procedure PRDC which should update this data into Table2. Below is my code for Trigger and procedure. The ID generated in trigger is not saving in procedure (it is again incrementing by one). Can you help me out with this issues? I want to save the records in Table 1 and Table2 with same ID. This should work multiple times if user is inserting multiple rows.
Trigger code:
ALTER TRIGGER [dbo].[TRG_Table1] ON [dbo].[Table1]
AFTER INSERT
AS
DECLARE @P_EVENT VARCHAR(max)
,@P_NEW_ID FLOAT(53)
,@P_N_CRTDATE DATETIME2(0)
,@P_TNAME VARCHAR(max)
,@P_NAME VARCHAR(max)
BEGIN
IF NOT EXISTS (
SELECT *
FROM deleted
)
set @P_NEW_ID = NEXT VALUE FOR dbo.SEQ_MASTER
set nocount on
SELECT @P_EVENT = 'I'
,@P_N_CRTDATE = CREATED_DATE
,@P_TNAME = '[dbo].[ME_ADAPTER_CU]'
,@P_NAME = NAME
FROM inserted
EXEC [dbo].[narendra] @P_EVENT
,@P_NEW_ID
,@P_N_CRTDATE
,@P_TNAME
,@P_NAME
end
Go
SEQUENCE CODE:
ALTER PROCEDURE PRDC
@P_EVENT varchar(max),
@P_NEW_ID float(53) output,
@P_N_CRTDATE datetime2(0),
@P_TNAME varchar(max),
@P_NAME varchar(max)
AS
BEGIN
IF @P_EVENT = 'I'
BEGIN
INSERT dbo.TABLE2(TABLENAME, ID, NAME)
VALUES (@P_TNAME, @P_NEW_ID, @P_NAME)
Return @P_NEW_ID
END
END
Thanks a lot in advance.
May 21, 2015 at 7:43 am
You still haven't posted the ddl for the base tables here so we can build this on our systems to test with. Your code now is further from accomplishing the requirements. It is now only for inserts. It now only processes a single row no matter how many are inserted. This whole process needs a complete rethinking of how to accomplish what you want. With a clear understanding of what you are trying to do this should be pretty simple. I am guessing that a single insert statement is all you need in your trigger. No variables, looping etc. Give us the details of your tables and we can help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 21, 2015 at 8:04 am
1. Throw the trigger away.
2. In the trigger, you do this code:
set @P_NEW_ID = NEXT VALUE FOR dbo.SEQ_MASTER
Do this in the insert procedure!
You have the sequence, do the insert into table 1 and then update table 2
And, like Sean has been asking, please post your DDL for the tables.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply