Results 1 to 10 of 10

Thread: triggers....

  1. #1
    Join Date
    May 2004
    Posts
    36

    Question Unanswered: triggers....

    i am inserting data into a table on my database and trying to create a trigger that will use the information from this table to update two other tables so i dont' have to do three different inserts.

    my problem is that i have created the trigger, but i can't get it to work after i insert the information into my table.... how do i get the trigger to fire??????

  2. #2
    Join Date
    Feb 2004
    Location
    Rhode Island
    Posts
    69

    Triggers

    1. Triggers will be fired automatically

    2. Are those table on the same server or database?. If not, can this server connect to the other server

    3. Are you pulling records from inserted inside the trigger T-SQL

  3. #3
    Join Date
    May 2004
    Posts
    36
    yes all the tables are on the same database. i'm not using from inserted.... how does that work??

  4. #4
    Join Date
    Feb 2004
    Location
    Rhode Island
    Posts
    69

    Trigger

    DECLARE @RCD_ID INTEGER
    DECLARE @InsertedCount INTEGER
    DECLARE @DeletedCount INTEGER

    SET @InsertedCount = (SELECT COUNT(*) FROM inserted)
    SET @DeletedCount = (SELECT COUNT(*) FROM deleted)

    IF (@InsertedCount > 0)
    BEGIN
    IF @DeletedCount > 0 -- WHEN A RECORD IS UPDATEd
    SELECT xxxxxxxx FROM deleted
    ELSE -- WHEN A New record added.
    SELECT xxxxxxxx FROM inserted

    ***** you may want use your logic here ***********

    END

  5. #5
    Join Date
    May 2004
    Posts
    36
    thanks..... i'll give that a try.

  6. #6
    Join Date
    May 2004
    Posts
    36
    here is what i'm trying to do, but i cant' get the insert to work after i've inserted the information into the first table.....





    DECLARE @recordtype char(2)
    DECLARE @PickWave char(11)
    DECLARE @PackWave char(14)

    DECLARE @RCD_ID INTEGER
    DECLARE @InsertedCount INTEGER
    DECLARE @DeletedCount INTEGER

    SET @InsertedCount = (SELECT COUNT(*) FROM inserted)
    SET @DeletedCount = (SELECT COUNT(*) FROM deleted)

    IF (@InsertedCount > 0)
    BEGIN

    select @recordtype = recordtype, @PickWave = piwid, @PackWave = pawid, from inserted

    IF @recordtype = '01'
    BEGIN
    set @counter = 0
    while (@counter < @Quantity)
    BEGIN
    INSERT INTO TAble2 VALUES (@PickWave,@PickWave)
    END
    END

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Nope...you need to think in terms of set processing....

    And where's the CREATE TRIGGER myTrigger99

    syntax?

    Do you have

    FOR INSERT, UPDATE, DELETE

    ?????
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    May 2004
    Posts
    36
    sorry i left that out, but yes i do have create trigger and for insert, update, delete......

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Still you need to think in terms of sets....



    Code:
    DECLARE @InsertedCount INTEGER
    DECLARE @DeletedCount INTEGER
    
    SET @InsertedCount = (SELECT COUNT(*) FROM inserted)
    SET @DeletedCount = (SELECT COUNT(*) FROM deleted)
    
    IF (@InsertedCount > 0)
      BEGIN
    	INSERT INTO Table2 (piwid, pawid) 
    	SELECT piwid, pawid 
    	  FROM inserted
    	 WHERE recordtype = '01'
      END
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    May 2004
    Posts
    36
    ok... i think i understand what i need to do now, thanks........

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •