Update Trigger after Insert - 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

Update Trigger after Insert

Author

Topic

zero1de
Posting Yak Master

105 Posts

Posted - 2012-03-02 : 06:28:22

Hi,
I need a trigger that fire after insert or update on tabel Immo to update some Fields. Since I'm still a beginner, I started what but it does not work!

THX for your help

Tabel insert:

CREATE TABLE [dbo].[IMMO] (
[DWDOCID] int NOT NULL,
[ENGAGEMENTNR] nvarchar(40) NULL,
[NACHNAME] nvarchar(40) NULL,
[VORNAME] nvarchar(40) NULL,
[GEBURTSDATUM] datetime NULL,
[SPARTE] nvarchar(40) NULL,
[PRODUNIT] int NULL,
[PARTNERID] int NULL,
[DOKDATUM] datetime NULL,
[FORDNR] nvarchar(20) NULL,
[BARCODENR] nvarchar(38) NULL,
[BRIEFID] nvarchar(20) NULL,
ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
WITH (DATA_COMPRESSION = NONE);
GO

Tabel get the Update Information:

CREATE TABLE [dbo].[TBL_SC_PARTNER] (
[PRODUNIT] nvarchar(384) NULL,
[FORDNR] nvarchar(50) NULL,
[FORDERGNR] int NULL,
[GLAEUBIGERNR] int NULL,
[PARTNERID] nvarchar(384) NOT NULL,
[NAME2] varchar(35) NULL,
[NAME3] varchar(35) NULL,
[NAME1] varchar(35) NOT NULL,
[STRASSE] varchar(35) NULL,
[PLZ] varchar(6) NULL,
[ORT] varchar(28) NULL,
[GEBURTSDATUM] datetime NULL,
[BEZSPARTE] varchar(40) NULL,
[AKTENEIGNER] nvarchar(384) NOT NULL,
[ENGAGEMENT] varchar(25) NULL,
[PARTNERIDALT] nvarchar(384) NULL)
ON [PRIMARY]
WITH (DATA_COMPRESSION = NONE);
GO

CREATE TRIGGER [dbo].[trig_update_Immo_Meta] ON [dbo].[IMMO]
WITH EXEC AS CALLER
AFTER INSERT
AS
DECLARE @Fordnr varchar(20)
SET @Fordnr = (SELECT Fordnr FROM inserted)
BEGIN

UPDATE IMMO
SET NACHNAME =
(SELECT i.NACHNAME
FROM dwdata.TBL_SC_PARTNER.Name1 t
WHERE t.Fordnr = @Fordnr),
Vorname =
(SELECT i.Vorname
FROM dwdata.TBL_SC_PARTNER.Name1 t
WHERE t.Fordnr = @Fordnr),
Engagementnr =
(SELECT i.Engagementnr
FROM dwdata.TBL_SC_PARTNER.Name1 t
WHERE t.Fordnr = @Fordnr),
PARTNERID =
(SELECT i.PARTNERID
FROM dwdata.TBL_SC_PARTNER.Name1 t
WHERE t.PARTNERID = @Fordnr),
Stapelnummer = 'IMP_2012'

PRINT ('Befehl wurde ausgeführt.')
END
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-02 : 12:24:21

the way you're written it, you're assuming its always single row update

you need to change it like below for batch updates


CREATE TRIGGER [dbo].[trig_update_Immo_Meta] ON [dbo].[IMMO]
WITH EXEC AS CALLER
AFTER INSERT
AS
...

UPDATE i
SET ... 
FROM IMMO i
INNER JOIN dwdata.TBL_SC_PARTNER.Name1 t
ON t.PARTNERID = i.PARTNERID 
INNER JOIN inserted i
ON i.Fordnr = t.PARTNERID 
END
GO

However i'm not clear with your logic

you're creating trigger on IMMO and updating value of same table. didnt understand reason for that
Also whats the significance of the dwdata.TBL_SC_PARTNER table? I cant even see any columns of it used in update at all!

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

zero1de
Posting Yak Master

105 Posts

Posted - 2012-03-02 : 13:18:25

Once an entry is in the table Immo with the condition "Briefid is not null" then the trigger should get the rest information from the table tbl_sc_partner and updated immo.
By Fordnr or Partnerid I can match the fields and other fields such as surname, first name, etc., with the data from the table tbl_sc_partner accumulate in immo.

THX

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-03-02 : 13:25:37

I'd suggest that this is not a good use of a trigger. Rather, it sounds like a process problem. Whatever is inserting into Immo should either a) have the information from tbl_sc_partner or b) a sproc should be used to get that information on insert. Just my 2 bits.

zero1de
Posting Yak Master

105 Posts

Posted - 2012-03-03 : 10:42:11

Understand. How would this process be ? Can you show me how to do this ? Ther will be only 3 fields updating in the table Immo (Engagenmentnr, Name1 and Name2) via the key Partnerid or Fordnr over tbl_sc_partner. The balance has to be a very timely manner!

THX for your help :)

zero1de
Posting Yak Master

105 Posts

Posted - 2012-03-04 : 08:09:46

You mean that about so: Only when the Procedure supposed to be exported? I need the data direct after the insert and procedure can not run after a event only chronological or ?

CREATE PROCEDURE dbo.upd_Immo_Meta
(@Fordnr nvarchar(50), @Partnerid nvarchar(50))
AS

IF EXISTS(SELECT *
FROM dbo.tbl_sc_partner t
WHERE Fordnr = @Fordnr OR
left(PARTNERID,len(PARTNERID)-1) = @Partnerid)
Begin
UPDATE dbo.Immo
set ENGAGEMENTNR = (select ENGAGEMENTNR from TBL_SC_PARTNER where FORDNR=@Fordnr),
Nachname = (select Name1 from TBL_SC_PARTNER where FORDNR=@Fordnr) ,
VOrname = (select Name2 from TBL_SC_PARTNER where FORDNR=@Fordnr),
Stapelnummer ='o.k'
WHERE Fordnr = @Fordnr Or left(PARTNERID,len(PARTNERID)-1) = @Partnerid
PRINT 'UPDATE performed. '
END

ELSE
BEGIN
PRINT 'No row found !'
END

EXEC dbo.upd_Immo_Meta '2310510394Z','1401'

zero1de
Posting Yak Master

105 Posts

Posted - 2012-03-04 : 08:11:38

and how can the procedure one give the right Fordnr or Partnerid each time ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-04 : 10:17:46

quote:
Originally posted by zero1de

and how can the procedure one give the right Fordnr or Partnerid each time ?

how will intial insert to immo table occur? is there not a way to club this updation also in the same proc?

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

zero1de
Posting Yak Master

105 Posts

Posted - 2012-03-04 : 15:17:34

Unfortunately no the insert to come through the application and not over stored procedure

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources