Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2009
    Posts
    262

    Unanswered: Update Trigger Help . ( table structure & insert included)

    Test_Order table

    Code:
    GO
    CREATE TABLE [dbo].[test_order](
    	[id] [int] IDENTITY(1,1) NOT NULL,
    	[pemail] [varchar](250) NULL,
    	[orderstatus] [int] NULL
    ) ON [PRIMARY]
    GO
    Inserts
    Code:
    go
    INSERT INTO [Test].[dbo].[test_order]
               ([pemail]
               ,[orderstatus])
         VALUES
               ('mishaalsy@yahoo.ca'
               ,2)
    go
    go
    INSERT INTO [Test].[dbo].[test_order]
               ([pemail]
               ,[orderstatus])
         VALUES
               ('fowadsy@yahoo.ca'
               ,2)
    go

    Test_pendingtask table
    Code:
    GO
    CREATE TABLE [dbo].[test_pendingtask](
    	[TaskID] [int] IDENTITY(1,1) NOT NULL,
    	[AuthTaskIDFK] [smallint] NOT NULL,
    	[OrderID] [int] NOT NULL,
    	[TaskDate] [smalldatetime] NOT NULL,
    	[isFinished] [bit] NULL,
    	[FinishedDate] [smalldatetime] NULL,
    	[SentDetails] [varchar](1024) NULL,
    	[SentDate] [smalldatetime] NULL
    ) ON [PRIMARY]
    
    GO
    Insertes for it

    Code:
    go
    INSERT INTO [Test].[dbo].[test_pendingtask]
               ([AuthTaskIDFK]
               ,[OrderID]
               ,[TaskDate]
               ,[isFinished]
               ,[FinishedDate]
               ,[SentDetails]
               ,[SentDate])
         VALUES
               (1,1,getdate(), 1,'2011-01-04',null,null)
    go
    go
    INSERT INTO [Test].[dbo].[test_pendingtask]
               ([AuthTaskIDFK]
               ,[OrderID]
               ,[TaskDate]
               ,[isFinished]
               ,[FinishedDate]
               ,[SentDetails]
               ,[SentDate])
         VALUES
               (2,1,getdate(), 1,'2011-01-04',null,null)
    go


    Trigger on table ( last guy put it there

    Code:
    Create TRIGGER [dbo].[TRG_OrdersLog]
       ON  [dbo].[test_Order] for UPDATE
    AS 
    BEGIN
    
    	SET NOCOUNT ON;	
    	declare @OrderID as int,
      		  @OrderStatus as int
    			
    	
    	select @OrderID=id,@OrderStatus=isnull(orderstatus,0) FROM INSERTED
    	if @OrderStatus=2	
    	begin	
    	insert into LogDB.dbo.orderslog select *,getdate() from orders     where id=@OrderID
    	if @OrderStatus=2	
    	end
    
    END

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in a previous thread you asked why your posts often go unanswered

    i'll give you an example of why -- in this thread, you have done a good job of setting up the test case, but unfortunately you neglected to mention what your problem is

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2009
    Posts
    262
    Logic :
    As an update trigger it is , after IF ( in Else clause)
    a procedure is to be added to update test_pendingtask table (column : Finisheddate)

    Bunsiness logic: when an order is updated and the new email ( modified) email address entered if not equal to old value . update pendingtask table and make finisheddate=null
    finisheddate determines if emails should be send or not ( if null it wil be sent again .. and then logged in pending tasktable as datetime of sent )

    i made this procedure in start

    Code:
    ALTER proc [dbo].[CC_update_order] 
    @Ud_orderid int,@UD_pemail char(50)
    as
    begin
           declare 		@pemail char(50),
    				@TRG_MAIL CHAR(50)
    		select @TRG_MAIL =@ud_pemail
    		select @pemail=pemail from test_order where id=@ud_orderid
    		IF @pemail!=@TRG_MAIL --and @newstatus='A'
    		begin	
    	        update test_pendingtask 
    		set finisheddate=null
    		where orderid=@Ud_orderid 
    		end
    
    end

    this procedure is nested in the trigger in this way

    Code:
    ALTER TRIGGER [dbo].[TRG_Orders]
       ON  [dbo].[Test_Orders] for UPDATE
    AS 
    BEGIN
    
    	SET NOCOUNT ON;	
    	declare @OrderID as int,
    			@OrderStatus as int,
    			@p_email char(50)
    	
    
    	select @OrderID=id,@OrderStatus=isnull(orderstatus,0),@p_email=pemail  
    FROM INSERTED
    	/*
    	insert into CCT_LogDB.dbo.orderslog select *,getdate() from orders where id=@OrderID*/
    	if @OrderStatus=2	
    	begin	
    		Exec cc_update_order @orderid,@p_email
    	end
    
    END


    now the problem :


    it is completing with no error . the trigger and the procedure
    but updating no row .

    i tested the code using hard codded values .. i.e

    Code:
    declare
    @amail varchar (100),
    @bmail varchar(100)
    set @bmail='fowadsy@yahoo.ca'
    select @amail=pemail from orders where id=96523
    
    		--select @UD_OrderID=id,@UD_pemail=pemail from test_order 
    		IF @amail!=@bmail-- @updated_pemail!=@ud_pemail --and @newstatus='A'
    		begin	
    	    update test_pendingtask 
    		set finisheddate=null
    		where orderid=96523--@Ud_orderid 
    		end
    its updating this way

    but when i update to fire this trigger .. or even when insert or modify or any thing in the test_order table .. nothing happen in test_pending task


    help please .

  4. #4
    Join Date
    Aug 2009
    Posts
    262
    For the length of post i was making another post for the problem part ..
    i wasnt sure of the allowed length of the post
    wish u had waited just for a min .
    Last edited by mishaalsy; 01-05-11 at 07:43.

  5. #5
    Join Date
    Aug 2009
    Posts
    262
    So far what i have done is this .. and i am lost i admit.

    The procedure .

    Code:
    ALTER proc [dbo].[CC_update_order] 
    @Ud_orderid int,@UD_pemail char(50)
    as
    begin
            declare @pemail char(50),@TRG_MAIL CHAR(50),@order_id int
    		select @TRG_MAIL =@ud_pemail , @order_id=@ud_orderid
    		select @pemail=pemail from test_order where id=@ud_orderid
    				--select @UD_OrderID=id,@UD_pemail=pemail from test_order 
    		IF @pemail!=@TRG_MAIL --and @newstatus='A'
    		begin	
    	    update test_pendingtask 
    		set finisheddate=null
    		where orderid=@Ud_orderid 
    		end
    
    end
    And the Trigger

    Code:
    alter TRIGGER [dbo].[TRG_test_Order]
       ON  [dbo].[test_Order] for UPDATE
    AS 
    BEGIN
    
    	SET NOCOUNT ON;	
    	declare @OrderID as int,
    			@OrderStatus as int,
    			@p_email char(50)
    	
    	SELECT @orderid=b.id,@OrderStatus=isnull(b.orderstatus,0),@p_email=b.pemail 
    	FROM inserted a
      INNER JOIN deleted b ON a.id=b.id
    	where a.id=@orderid 
    	--select @OrderID=id,@OrderStatus=isnull(orderstatus,0) FROM INSERTED
    	--insert into CCT_LogDB.dbo.orderslog select *,getdate() from orders where id=@OrderID
    	if @OrderStatus=2	
    	begin	
    			/*Exec USP_CREDITSOFT_UPDATE_EWHITEBOARD @OrderID
    			exec cc_update_order @orderid,@p_email
    	end
    	else 
    		begin
    		-- declare @order_id int, @pemail varchar(50)
    		SELECT @orderid=a.id,@p_email=a.pemail FROM inserted a
            INNER JOIN deleted b ON a.id=b.id
    		where a.id=@orderid  */
    
    		exec cc_update_order @orderid,@p_email
    	
    end
    will this server an an update trigger ..
    i have tried reading for update rows identification
    INSERT: deleted has no records, inserted has records
    DELETE deleted has records, inserted has no records
    UPDATE: both tables have records.

    thats why i am joining inserted and deleted ..

    point me towards right direction please

    thanks in advance
    Last edited by mishaalsy; 01-05-11 at 09:10. Reason: eddited join to inner join

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Yeah, you see how messy it can quickly become? Also see my response to your other post about "the rat". I wouldn't touch this trigger-infested environment myself, but would advise to start minimizing the number of triggers that are flying around like bats in a cave. This is just wrong to have a trigger within an RDBMS that fully supports DRI and code-based objects. The only exception (which I still don't alow) is when you don't know who is touching your data and when this is happening. But now you know that this is happening, so go back and stop this nonsense!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Aug 2009
    Posts
    262
    rdjabarov i can say u are right that the one who made that trigger is the rat .. but that rat got fired in last week of december.

    Changing process is not an option for me. Having no trigger is not an option either . I am replicating an online database and adjusting minor flaw in execution. i.e procedure not working as required or trigger not executing .. etc

    If i were to make it from scratch i would have got rid of triggers and use another approach. Even in oracle we triggers as much as u hate them here in sql.

    any way i got it working ... had to simplify it .

    now modifying it for its actual purpose ... which ever it was i seem not to see it but managers do .

  8. #8
    Join Date
    Aug 2009
    Posts
    262
    Trigger:
    Code:
    ALTER TRIGGER [dbo].[TRG_test_Order]
       ON  [dbo].[test_order] for UPDATE
    AS 
    BEGIN
    
    	SET NOCOUNT ON;	
    	declare @OrderID as int,
    			@OrderStatus as int,
    			@p_email char(50)
    	
    	SELECT @orderid=id,@OrderStatus=isnull(orderstatus,0),@p_email=pemail 
    	FROM inserted --where id=@orderid 
    	--select @OrderID=id,@OrderStatus=isnull(orderstatus,0) FROM INSERTED
    	--insert into CCT_LogDB.dbo.orderslog select *,getdate() from orders where id=@OrderID
    	--if @orderid= 96572--@p_email is not null-- @OrderStatus=2	
    	begin	
         if @p_email is not null
    	begin
    		/*update test_pendingtask 
    		set finisheddate=null
    		where orderid=@orderid 	
    end		
    
    Exec USP_CREDITSOFT_UPDATE_EWHITEBOARD @OrderID
    			exec cc_update_order @orderid,@p_email
    	end
    	else 
    		begin
    		-- declare @order_id int, @pemail varchar(50)
    		SELECT @orderid=a.id,@p_email=a.pemail FROM inserted a
            INNER JOIN deleted b ON a.id=b.id
    		where a.id=@orderid  */
    		exec cc_update_order @orderid,@p_email
    	
    end
    END
    end


    and the procedure

    Code:
    ALTER proc [dbo].[CC_update_order] 
    @Ud_orderid int,@UD_pemail char(50)
    as
    begin
    
    		IF @ud_orderid is not null and @ud_pemail is not null
    		begin	
    	    update test_pendingtask 
    		set finisheddate=null
    		where orderid=@Ud_orderid 
    		end
    
    end
    i have to use inserted caz some how when an update occur it is not available in Deleted and i hate this "somehow" .. researching it now what exactly tha heaven is happening .

  9. #9
    Join Date
    Aug 2009
    Posts
    262
    its working .


    Code:
    ALTER TRIGGER [dbo].[TRG_test_Order]
       ON  [dbo].[test_order] for UPDATE
    AS 
    BEGIN
    
    	SET NOCOUNT ON;	
    	declare @OrderID as int,
    			@OrderStatus as int,
    			@p_email char(50),
    			@deleted_email  char(50)
    SELECT @orderid=a.id,@OrderStatus=isnull(a.orderstatus,0),@p_email=a.pemail,@deleted_email= b.pemail
    	FROM inserted a
    Inner Join deleted b
    on a.id=b.id
    	--SELECT @orderid=id,@OrderStatus=isnull(orderstatus,0),@p_email=pemail 
    	--FROM inserted --where id=@orderid 
    	--select @OrderID=id,@OrderStatus=isnull(orderstatus,0) FROM INSERTED
    	--insert into CCT_LogDB.dbo.orderslog select *,getdate() from orders where id=@OrderID
    	--if @orderid= 96572--@p_email is not null-- @OrderStatus=2	
    	begin	
         if @p_email!=@deleted_email
    	begin
    		/*update test_pendingtask 
    		set finisheddate=null
    		where orderid=@orderid 	
    end		
    
    Exec USP_CREDITSOFT_UPDATE_EWHITEBOARD @OrderID
    			exec cc_update_order @orderid,@p_email
    	end
    	else 
    		begin
    		-- declare @order_id int, @pemail varchar(50)
    		SELECT @orderid=a.id,@p_email=a.pemail FROM inserted a
            INNER JOIN deleted b ON a.id=b.id
    		where a.id=@orderid  */
    		exec cc_update_order @orderid,@p_email
    	
    end
    END
    end

Posting Permissions

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