TRIGGER PROB !! - 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

General SQL Server Forums

New to SQL Server Programming

TRIGGER PROB !!

Author

Topic

zero1de
Posting Yak Master

105 Posts

Posted - 2012-06-13 : 07:25:42

Hello, need a trigger, which will update the table Immo only when a new record is inserted and Field Matchcode has 203 entered.
My trigger, update the hole Table under certain circumstance, that I have not yet found.
Here is my Trigger
Update after insert
CREATE TRIGGER [dbo].[trigger_update_Matchcode_EMail]
ON [dbo].[IMMO]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
IF exists(SELECT 1 FROM immo t
JOIN INSERTED i
ON t.dwdocid = i.dwdocid
AND i.matchcode='203' and i.stapelnummer='Fax/E-Mail')
BEGIN
execute GetNewSeqVal_TBL_Sequence
UPDATE t
SET t.matchcode=(select max(SeqID) FROM TBL_Sequence)
FROM immo t
JOIN INSERTED i
ON t.dwdocid=i.dwdocid
END
end

Many THX

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-06-13 : 07:36:36

The update statement hasn't the same conditions as you have written in the exists().

No, you're never too old to Yak'n'Roll if you're too young to die.

zero1de
Posting Yak Master

105 Posts

Posted - 2012-06-13 : 08:53:06

CREATE TRIGGER [dbo].[trigger_update_Matchcode_EMail]
ON [dbo].[IMMO]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
IF exists(SELECT 1 FROM immo t
JOIN INSERTED i
ON t.dwdocid = i.dwdocid
AND i.matchcode='203' and i.stapelnummer='Fax/E-Mail')
BEGIN
execute GetNewSeqVal_TBL_Sequence
UPDATE t
SET t.matchcode=(select max(SeqID) FROM TBL_Sequence)
FROM immo t
JOIN INSERTED i
ON t.dwdocid=i.dwdocid

where i.matchcode='203' and i.stapelnummer='Fax/E-Mail')
[red]

you meen like this ?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-06-13 : 08:59:32

yes but in JOIN condition not in WHERE condition.

No, you're never too old to Yak'n'Roll if you're too young to die.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-13 : 08:59:39

What is the PK on immo?
If it is not dwdocid then your query will update all rows for that dwdocid on an inserrt - maybe that is what you want.

Your trigger only caters for single row inserts. Can multiple rows be added to the table from a single statement?
You don't need to join to the immo table in the exists check - everything will be in inserted.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-13 : 10:20:01

quote:
Originally posted by zero1de

Hello, need a trigger, which will update the table Immo only when a new record is inserted and Field Matchcode has 203 entered.
My trigger, update the hole Table under certain circumstance, that I have not yet found.
Here is my Trigger
Update after insert
CREATE TRIGGER [dbo].[trigger_update_Matchcode_EMail]
ON [dbo].[IMMO]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
IF exists(SELECT 1 FROM immo t
JOIN INSERTED i
ON t.dwdocid = i.dwdocid
AND i.matchcode='203' and i.stapelnummer='Fax/E-Mail')
BEGIN
execute GetNewSeqVal_TBL_Sequence
UPDATE t
SET t.matchcode=(select max(SeqID) FROM TBL_Sequence)
FROM immo t
JOIN INSERTED i
ON t.dwdocid=i.dwdocid
END
end

Many THX

Why cant you wrap this logic in acheck constraint created on column matchcode so that it will be taken care of in each insert automatically?

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

zero1de
Posting Yak Master

105 Posts

Posted - 2012-06-13 : 10:25:27

The PK is dwdocid
what you meen with on join ?
I have conditions on the table, which must be met before an update happens ? And I have only a join that is joint

quote:
Originally posted by webfred

yes but in JOIN condition not in WHERE condition.

No, you're never too old to Yak'n'Roll if you're too young to die.

zero1de
Posting Yak Master

105 Posts

Posted - 2012-06-13 : 10:29:47

Because is running on DMS System, and I cant change the DB-structure from outside. I can only create trigger sp, etc. .

quote:
Originally posted by visakh16

quote:
Originally posted by zero1de

Hello, need a trigger, which will update the table Immo only when a new record is inserted and Field Matchcode has 203 entered.
My trigger, update the hole Table under certain circumstance, that I have not yet found.
Here is my Trigger
Update after insert
CREATE TRIGGER [dbo].[trigger_update_Matchcode_EMail]
ON [dbo].[IMMO]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
IF exists(SELECT 1 FROM immo t
JOIN INSERTED i
ON t.dwdocid = i.dwdocid
AND i.matchcode='203' and i.stapelnummer='Fax/E-Mail')
BEGIN
execute GetNewSeqVal_TBL_Sequence
UPDATE t
SET t.matchcode=(select max(SeqID) FROM TBL_Sequence)
FROM immo t
JOIN INSERTED i
ON t.dwdocid=i.dwdocid
END
end

Many THX

Why cant you wrap this logic in acheck constraint created on column matchcode so that it will be taken care of in each insert automatically?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-13 : 10:33:12

quote:
Originally posted by zero1de

The PK is dwdocid
what you meen with on join ?
I have conditions on the table, which must be met before an update happens ? And I have only a join that is joint

quote:
Originally posted by webfred

yes but in JOIN condition not in WHERE condition.

No, you're never too old to Yak'n'Roll if you're too young to die.

i thing Fred was suggesting this for update


UPDATE t
SET t.matchcode=(select max(SeqID) FROM TBL_Sequence)
FROM immo t
JOIN INSERTED i
ON t.dwdocid=i.dwdocid 
and i.matchcode='203' 
and i.stapelnummer='Fax/E-Mail'

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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-13 : 11:19:11

As dwdocid is the pk then your query should work for single row inserts.
For multi row inserrts and if any one of them has i.matchcode='203' and i.stapelnummer='Fax/E-Mail' then it will update for every dwdocid that has been inserted.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

zero1de
Posting Yak Master

105 Posts

Posted - 2012-06-14 : 05:05:36

Many THX for your helps guys :) it works fine now

quote:
Originally posted by visakh16

quote:
Originally posted by zero1de

The PK is dwdocid
what you meen with on join ?
I have conditions on the table, which must be met before an update happens ? And I have only a join that is joint

quote:
Originally posted by webfred

yes but in JOIN condition not in WHERE condition.

No, you're never too old to Yak'n'Roll if you're too young to die.

i thing Fred was suggesting this for update

UPDATE t
SET t.matchcode=(select max(SeqID) FROM TBL_Sequence)
FROM immo t
JOIN INSERTED i
ON t.dwdocid=i.dwdocid
and i.matchcode='203'
and i.stapelnummer='Fax/E-Mail'

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-14 : 12:08:10

wc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources