CREATE TRIGGER [dbo].[updateinsert_DONEPO] 
ON [dbo].[PO_detail]
AFTER INSERT
AS 
BEGIN

SET NOCOUNT ON;

IF exists(SELECT 1 FROM tbl_product_list t
          JOIN INSERTED i
          ON t.productcode = i.prodcode 
          AND i.statusx='Done')

BEGIN
UPDATE t 
SET t.purchased=t.purchased + i.qty
FROM tbl_product_list t
JOIN INSERTED i
ON t.productcode=i.prodcode
END

ELSE
BEGIN
INSERT INTO tbl_product_list
([productcode],[description],[category],[packtype],[supplierprice],[srp],[begininvent],[onhand],[datetimeentered],
[enteredby],[status],[supplierid],[suppliername])
SELECT i.prodcode,i.proddesc,i.unit,i.packtype,i.price,i.srp,
i.qty,i.qty,i.podate,i.addedby,i.statusx,i.supplierid,i.supplier
FROM INSERTED i
END 
END 

GO

quote:

---

Originally posted by blocker

thank you very much guys as in.this works. How should i give credit.?

God bless us all.

---

quote:

---

Originally posted by tkizer

visakh, please do not post such wide queries. Moderators have to fix them to avoid horizontal bars. I edited your post to add a carriage return to fix it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

---

CREATE FUNCTION dbo.F_MapPath]
(
	@ID int = 1
)
RETURNS @MyTable TABLE (ID INT, CompletePath nvarchar(1024))
AS
BEGIN
	DECLARE @Parent INT
	SET     @Parent = (SELECT PARENT
					   FROM dbo.FolderAttachment
					   WHERE ID = @ID);
	WITH Tabela(ID, Parent, Name, Path)
	AS (SELECT ID, Parent, Name, CAST(Name AS nvarchar(1024))
        FROM   dbo.FolderAttachement
        WHERE  ID = @ID

        UNION ALL

        SELECT tb.ID, PA.Parent, PA.Name, CAST(PA.Name + '\' + tb.CompletePath AS nvarchar(1024)) 
        FROM   dbo.FolderAttachment AS PA INNER JOIN
                       Tabela AS tb ON PA.ID = tb.Parent
        WHERE  PA.Parent <= @Parent)

	INSERT	@MYTable
    SELECT ID, Path
    FROM   Tabela AS Tabela_1
    WHERE  Parent = 1

	RETURN
END

CREATE TRIGGER dbo.T_Upd_MapPath
   ON  dbo.FolderAttachment
   AFTER INSERT, DELETE, UPDATE
AS 
BEGIN
	DELETE FROM dbo.MapPath
		   WHERE (SELECT Id FROM DELETED) = Id

	INSERT INTO dbo.MapPath (Id, Path)
	SELECT * FROM dbo.F_MapPath(INSERTED.ID)
END
GO

quote:

---

Originally posted by visakh16

CREATE TRIGGER dbo.T_Upd_MapPath
   ON  dbo.FolderAttachment
   AFTER INSERT, DELETE, UPDATE
AS 
BEGIN
	DELETE FROM dbo.MapPath
		   WHERE (SELECT Id FROM DELETED) = Id

	INSERT INTO dbo.MapPath (Id, Path)
	SELECT f.* 
        FROM INSERTED i
        CROSS APPLY dbo.F_MapPath(i.ID) f
END
GO

In future, please post questions as a new thread rather than hijacking an earlier thread

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---