question about writting a Trigger

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

question about writting a Trigger

Author

Topic

ahmad.golshani
Starting Member

4 Posts

Posted - 2008-05-01 : 23:10:12

hello.

i have two table so that they are following form:

tblCardex(Fd_GoodsId,Fd_RepositoryId,Fd_DateTime,Fd_Type,Fd_UnitCoefficent)

tblGoodsStock(Fd_GoodsId,Fd_RepositoryId,Fd_UnitCoefficent)

Fd_GoodsId,Fd_RepositoryId -->inteager
Fd_UnitCoefficent --> decimal
Fd_Type --> bit (0 means input and 1 means output)

i wish writting a trigger on Insert of tblCardex.in this trigger ,i must do following operations:

if(tblGoodsStock.Fd_GoodsId=Inserted.Fd_GoodsId and tblGoodsStock.Fd_RepositoryId=Inserted.Fd_RepositoryId)
begin

if(Inserted.Fd_Type==0)
tblGoodsStock.Fd_UnitCoefficent = tblGoodsStock.Fd_UnitCoefficent+
Inserted.Fd_UnitCoefficent

else --Inserted.Fd_Type==1
tblGoodsStock.Fd_UnitCoefficent = tblGoodsStock.Fd_UnitCoefficent-
Inserted.Fd_UnitCoefficent

end

tank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-02 : 01:45:52

[code]CREATE TRIGGER tblCarder_I ON tbl_Cardex
AFTER INSERT
AS
BEGIN
UPDATE gs
SET gs.Fd_UnitCoefficent = gs.Fd_UnitCoefficent +
CASE WHEN i.Fd_Type=0 THEN i.Fd_UnitCoefficent ELSE (-1) * i.Fd_UnitCoefficent END
FROM tblGoodsStock gs
INNER JOIN INSERTED i
ON i.Fd_GoodsId=gs.Fd_GoodsId
and i.Fd_RepositoryId=gs.Fd_RepositoryId
END [/code]

ahmad.golshani
Starting Member

4 Posts

Posted - 2008-05-02 : 18:14:29

thanks.

for update trigger on tblCardex , i written folowing code. but it show error " incorrect syntax near from"
CREATE TRIGGER trg_tblCardex_U ON tblCardex
FOR UPDATE
AS
begin
if(Update(Fd_Type))
begin
update tblAlisName set tblAlisName.Fd_UnitCoefficent=tblAlisName.Fd_UnitCoefficent +
case
when i.Fd_Type=0 then d.Fd_UnitCoefficent
else (-1) * d.Fd_UnitCoefficent
end
+
case
when i.Fd_Type=0 then i.Fd_UnitCoefficent
else (-1) * i.Fd_UnitCoefficent
end
end
else
begin
update tblAlisName set tblAlisName.Fd_UnitCoefficent=tblAlisName.Fd_UnitCoefficent +
case
when i.Fd_Type=0 then (-1) * d.Fd_UnitCoefficent+ i.Fd_UnitCoefficent
else (-1) * i.Fd_UnitCoefficent + d.Fd_UnitCoefficent
end
end
from tblGoodsStock tblAlisName
Inner join Inserted i
Inner join Deleted d
on
i.Fd_GoodsId=tblAlisName.Fd_GoodsId and
d.Fd_GoodsId=tblAlisName.Fd_GoodsId and
i.Fd_RepositoryId=tblAlisName.Fd_RepositoryId and
d.Fd_RepositoryId=tblAlisName.Fd_RepositoryId
end

please guide me write trigger for update on tblCardex(After Update)
thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-03 : 01:13:01

Change it like this & try:-

CREATE TRIGGER trg_tblCardex_U ON tblCardex
FOR UPDATE
AS
begin
  if(Update(Fd_Type))
  begin
    update tblAlisName 
    set tblAlisName.Fd_UnitCoefficent=tblAlisName.Fd_UnitCoefficent +
         case when i.Fd_Type=0 then d.Fd_UnitCoefficent else (-1) *     d.Fd_UnitCoefficent end + case when i.Fd_Type=0 then         i.Fd_UnitCoefficent else (-1) * i.Fd_UnitCoefficent end
from tblGoodsStock tblAlisName
Inner join Inserted i
Inner join Deleted d
on
i.Fd_GoodsId=tblAlisName.Fd_GoodsId and
d.Fd_GoodsId=tblAlisName.Fd_GoodsId and
i.Fd_RepositoryId=tblAlisName.Fd_RepositoryId and
d.Fd_RepositoryId=tblAlisName.Fd_RepositoryId
end
else
begin
    update tblAlisName set tblAlisName.Fd_UnitCoefficent=tblAlisName.Fd_UnitCoefficent +
case when i.Fd_Type=0 then (-1) * d.Fd_UnitCoefficent+  i.Fd_UnitCoefficent
else (-1) * i.Fd_UnitCoefficent + d.Fd_UnitCoefficent end
from tblGoodsStock tblAlisName
Inner join Inserted i
Inner join Deleted d
on
i.Fd_GoodsId=tblAlisName.Fd_GoodsId and
d.Fd_GoodsId=tblAlisName.Fd_GoodsId and
i.Fd_RepositoryId=tblAlisName.Fd_RepositoryId and
d.Fd_RepositoryId=tblAlisName.Fd_RepositoryId
end
end

ahmad.golshani
Starting Member

4 Posts

Posted - 2008-05-03 : 13:25:30

this led to error :Incorrect Syntax near to keyword 'end'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-03 : 13:44:53

quote:
Originally posted by ahmad.golshani

this led to error :Incorrect Syntax near to keyword 'end'

CREATE TRIGGER trg_tblCardex_U ON tblCardex
FOR UPDATE
AS
begin
  if(Update(Fd_Type))
  begin
    update tblAlisName 
    set tblAlisName.Fd_UnitCoefficent=tblAlisName.Fd_UnitCoefficent +
         case when i.Fd_Type=0 then d.Fd_UnitCoefficent else (-1) *     d.Fd_UnitCoefficent end + case when i.Fd_Type=0 then         i.Fd_UnitCoefficent else (-1) * i.Fd_UnitCoefficent end
from tblGoodsStock tblAlisName
Inner join Inserted i
on
i.Fd_GoodsId=tblAlisName.Fd_GoodsId and
i.Fd_RepositoryId=tblAlisName.Fd_RepositoryId
Inner join Deleted d
on
d.Fd_GoodsId=tblAlisName.Fd_GoodsId and
d.Fd_RepositoryId=tblAlisName.Fd_RepositoryId
end
else
begin
    update tblAlisName set tblAlisName.Fd_UnitCoefficent=tblAlisName.Fd_UnitCoefficent +
case when i.Fd_Type=0 then (-1) * d.Fd_UnitCoefficent+  i.Fd_UnitCoefficent
else (-1) * i.Fd_UnitCoefficent + d.Fd_UnitCoefficent end
from tblGoodsStock tblAlisName
Inner join Inserted i
on i.Fd_GoodsId=tblAlisName.Fd_GoodsId
and i.Fd_RepositoryId=tblAlisName.Fd_RepositoryId
Inner join Deleted d
on d.Fd_GoodsId=tblAlisName.Fd_GoodsId 
and d.Fd_RepositoryId=tblAlisName.Fd_RepositoryId
end
end

You put the on condition in wrong place. try this

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources