Conditions in trigger - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Conditions in trigger

Author

Topic

Standos424
Starting Member

3 Posts

Posted - 2010-04-26 : 14:34:22

Hi, i need help with some trigger. When table SKzPol is empty i want insert record, but if this table isn't empty i want actualize record. I need help with query, because i don't know how to resolve.

Thank you very much.

Query:

CREATE TRIGGER dbo.actualize
ON dbo.SKPP
AFTER INSERT
AS

UPDATE SKz SET Vol=20
WHERE Hmotnost <> NULL AND RefSklad=1

IF SKzPol IS NOT NULL
then UPDATE UPDATE SKzCnPol SET ProdejC=Hmotnost
FROM
SKz LEFT JOIN SKzPol ON (SKz.ID = SKzPol.RefAg) LEFT JOIN SKzCnPol ON (SKzPol.ID = SKzCnPol.RefAg)
WHERE RefSkCeny=1

Else

INSERT INTO SKzPol
(RefAg,RefSKz,Mnozstvi,OrderFld,RelTypPol,PrijemSZ,NevysklSZ,UpravaCen)
SELECT SKz.ID,SKz.ID,1,1,1,0,0,-1
FROM SKz LEFT JOIN SKzPol ON SKz.ID=SKzPol.RefSkz

UPDATE SKzCn SET ProdejC=SKz.ProdejDPH
FROM SKz INNER JOIN SKzCn ON SKzCn.RefAg=SKz.ID
WHERE SKz.RefSKlad=1 AND SKzCn.RefSkCeny=1

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2010-04-26 : 15:04:08

Use MERGE
[url]http://technet.microsoft.com/en-us/library/bb510625.aspx[/url]

quote:
Originally posted by Standos424

Hi, i need help with some trigger. When table SKzPol is empty i want insert record, but if this table isn't empty i want actualize record. I need help with query, because i don't know how to resolve.

Thank you very much.

Query:

CREATE TRIGGER dbo.actualize
ON dbo.SKPP
AFTER INSERT
AS

UPDATE SKz SET Vol=20
WHERE Hmotnost <> NULL AND RefSklad=1

IF SKzPol IS NOT NULL
then UPDATE UPDATE SKzCnPol SET ProdejC=Hmotnost
FROM
SKz LEFT JOIN SKzPol ON (SKz.ID = SKzPol.RefAg) LEFT JOIN SKzCnPol ON (SKzPol.ID = SKzCnPol.RefAg)
WHERE RefSkCeny=1

Else

INSERT INTO SKzPol
(RefAg,RefSKz,Mnozstvi,OrderFld,RelTypPol,PrijemSZ,NevysklSZ,UpravaCen)
SELECT SKz.ID,SKz.ID,1,1,1,0,0,-1
FROM SKz LEFT JOIN SKzPol ON SKz.ID=SKzPol.RefSkz

UPDATE SKzCn SET ProdejC=SKz.ProdejDPH
FROM SKz INNER JOIN SKzCn ON SKzCn.RefAg=SKz.ID
WHERE SKz.RefSKlad=1 AND SKzCn.RefSkCeny=1

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-26 : 15:36:58

Won't you need to use an INSTEAD OF trigger along with MERGE?

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

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources