Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# Transactional trigger action

Posted on 2004-03-28
Medium Priority
61 Views

Toys_box    Toy_box_id,Toy_type_id(FK from Toy_Type table) ,Start_serial,End_serial  ==  (Box which contains toys)

Toy         Toy_Id,Toy_box_ID(FK from Toys_Box) Toy_Name,Toy_Type_id(Fk from toy_Type table),Toy_serial_Number
Toy_Type    Toy_type_ID,Toy_Color

Question : When a Record is Entered into the Toys_Box table  a Transactional trigger has to be fired to enter a Records  in Toy Table ..... (toy_Serial_number column should be filled up with) ........Lets say in the table toys_box we have start_Serial  is 1001 to end_Serial 1010  then all the  serial_numbers 1001 to 1010 should be entered into the toy table

Pls let me know how this trigger can be written
0
Question by:sainavya1215
• 7
• 4

LVL 70

Accepted Solution

Scott Pletcher earned 2000 total points
ID: 10704496

ON toys_box
AFTER INSERT
AS
IF @@ROWCOUNT = 0
RETURN
DECLARE toysBoxCsr CURSOR READ_ONLY FAST_FORWARD FOR
SELECT toy_box_id, toy_type_id, start_serial, end_serial
FROM inserted WITH (NOLOCK)
DECLARE @toy_box_id INT
DECLARE @toy_type_id INT
DECLARE @start_serial INT
DECLARE @end_serial INT
OPEN toysBoxCsr
FETCH NEXT FROM toysBoxCsr INTO @toy_box_id, @toy_type_id, @start_serial, @end_serial
WHILE @@FETCH_STATUS = 0
BEGIN
WHILE @start_serial <= @end_serial
BEGIN
INSERT INTO toy (toy_box_id, toy_name, toy_type_id, toy_serial_number)
VALUES(@toy_box_id, '?', @toy_type_id, @start_serial)
SET @start_serial = @start_serial + 1
END --WHILE
FETCH NEXT FROM toysBoxCsr INTO @toy_box_id, @toy_type_id, @start_serial, @end_serial
END --WHILE
CLOSE toysBoxCsr
DEALLOCATE toysBoxCsr
GO
0

Author Comment

ID: 10706514
hi,
I just saw the posted message.I will try out ....Is it better if we go with a trigger or implement a stored procedure (using transaction)by invoking it from the application...

0

LVL 70

Expert Comment

ID: 10707080
It depends.  If it's just for an INSERT (as shown above), I would say use a trigger.

If you need to do it for UPDATEs, and you often change the data, you might be better off with a process that you could trigger yourself or that ran on a set schedule.
0

Author Comment

ID: 10730372
hi Scott,

I am extremely sorry about the late reply..I had a virus on my system known as Iexplorer.exe in XP.. When I open the Browser it closes within couple of minutes >I was working on that and could solve it.

I did check the code posted by u. Works excellent. Thx a lot . It was wonderful
0

Author Comment

ID: 10730386
Sorry forgot to ask u something : if insert fails in Toys_box will it rollback?
0

LVL 70

Expert Comment

ID: 10732426
The trigger gets control only after the INSERT to toys_box has completed, so there is no rollback issue related to toys_box.

If the INSERT to toy fails, and you want to rollback in that case, you can use the code below.

Also, forgot to mention before, I wasn't sure what to put for the toy name, so I used '?' as a "dummy" value that you could replace with whatever is correct.

WHILE @@FETCH_STATUS = 0
BEGIN
WHILE @start_serial <= @end_serial
BEGIN
INSERT INTO toy (toy_box_id, toy_name, toy_type_id, toy_serial_number)
VALUES(@toy_box_id, '?', @toy_type_id, @start_serial)
IF @@ERROR <> 0
BEGIN
RAISERROR('Error during Insert to Toy table', 16, 1)
ROLLBACK
END
SET @start_serial = @start_serial + 1
END --WHILE
FETCH NEXT FROM toysBoxCsr INTO @toy_box_id, @toy_type_id, @start_serial, @end_serial
END --WHILE
0

Author Comment

ID: 10732664
Thx once again .........I appreciate that
0

Author Comment

ID: 10765403
Hi ScottPletcher,

I have posted a NEW Question on the same tables Pls let me know ASAP it would be greatly appreciated. Thx

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20945034.html

0

Author Comment

ID: 10765440
sorry there is a timestamp column too in History table ........
0

LVL 70

Expert Comment

ID: 10765870
CORRECTION:

>> The trigger gets control only after the INSERT to toys_box has completed, so there is no rollback issue related to toys_box. <<

Actually a rollback in the trigger will rollback the inserts to the main table, even though they have already occurred from the viewpoint of the trigger.  Sorry, that must have been too early in the morning for me :-).
0

Author Comment

ID: 10800277
HI SCOTT,

Trigger works fine .. I am having a problem in getting the ID ............On the box table i have a stored proceudre when inserts a record it gets the ID value from my code. but instead of bringing the ID it gets the ID of the lastest id from toy table(as we know we have written a trigger that toy table is populated )

I think trigger is firing immediately after insert which is right... BUT WHY IS THAT IN MY CODE I AM GETTING THE ID OF TOY TABLE INSTEAD OF TOY_BOX NEW ID

0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differeâ€¦
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Acâ€¦
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
###### Suggested Courses
Course of the Month9 days, 14 hours left to enroll