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

x
?
Solved

Transactional trigger action

Posted on 2004-03-28
11
Medium Priority
?
61 Views
Last Modified: 2012-06-08

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
Comment
Question by:sainavya1215
  • 7
  • 4
11 Comments
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 10704496
Please try this:


CREATE TRIGGER toys_box_trg_Add_Toys
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

by:sainavya1215
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

by:Scott Pletcher
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:sainavya1215
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

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

Expert Comment

by:Scott Pletcher
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

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

Author Comment

by:sainavya1215
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

by:sainavya1215
ID: 10765440
sorry there is a timestamp column too in History table ........
0
 
LVL 70

Expert Comment

by:Scott Pletcher
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

by:sainavya1215
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question