Accepting Null values in Trigger

The trigger below was automatically generated when I transferred my data from
Access to SQL. Right now it needs to find a match between
Dressing.Dressing_ID and inserted.Day_Dressing_ID, that's great EXCEPT I want
to put in an exception to say insert if Dressing.Dressing_ID =
inserted.Day_Dressing_ID or inserted.Day_Dressing_ID is null. I can't get
the syntax right. Any thoughts?
/* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
inserted.Day_Dressing_ID))
BEGIN
RAISERROR 44447 'The record can''t be added or changed. Referential
integrity rules require a related record in table ''Dressing''.'
ROLLBACK TRANSACTION
END
You mean that : ?
SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
> inserted.Day_Dressing_ID OR inserted.Day_Dressing_ID is null)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Can" <Can@.discussions.microsoft.com> schrieb im Newsbeitrag
news:E19D9F15-A048-404F-9610-6826BF9E61D6@.microsoft.com...
> The trigger below was automatically generated when I transferred my data
> from
> Access to SQL. Right now it needs to find a match between
> Dressing.Dressing_ID and inserted.Day_Dressing_ID, that's great EXCEPT I
> want
> to put in an exception to say insert if Dressing.Dressing_ID =
> inserted.Day_Dressing_ID or inserted.Day_Dressing_ID is null. I can't get
> the syntax right. Any thoughts?
> /* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
> IF (SELECT COUNT(*) FROM inserted) !=
> (SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
> inserted.Day_Dressing_ID))
> BEGIN
> RAISERROR 44447 'The record can''t be added or changed. Referential
> integrity rules require a related record in table ''Dressing''.'
> ROLLBACK TRANSACTION
> END
>
|||Try,
/* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
IF (SELECT COUNT(*) FROM inserted where Day_Dressing_ID is not null) !=
(SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
inserted.Day_Dressing_ID))
BEGIN
RAISERROR 44447 'The record can''t be added or changed. Referential
integrity rules require a related record in table ''Dressing''.'
ROLLBACK TRANSACTION
END
AMB
"Can" wrote:

> The trigger below was automatically generated when I transferred my data from
> Access to SQL. Right now it needs to find a match between
> Dressing.Dressing_ID and inserted.Day_Dressing_ID, that's great EXCEPT I want
> to put in an exception to say insert if Dressing.Dressing_ID =
> inserted.Day_Dressing_ID or inserted.Day_Dressing_ID is null. I can't get
> the syntax right. Any thoughts?
> /* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
> IF (SELECT COUNT(*) FROM inserted) !=
> (SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
> inserted.Day_Dressing_ID))
> BEGIN
> RAISERROR 44447 'The record can''t be added or changed. Referential
> integrity rules require a related record in table ''Dressing''.'
> ROLLBACK TRANSACTION
> END
>
|||Jens,
[vbcol=seagreen]
This condition is saying to join a row from table Dressing to a row in table
inserted if the column Dressing_ID are equal in both tables or
inserted.Day_Dressing_ID is null, so if Dressing.Dressing_ID = 1 and
inserted.Dressing_ID is null those rows are joined.
This will not give you what you expect, see an example.
use northwind
go
create table t1 (
c1 int unique
)
go
create table t2 (
c1 int null
)
go
insert into t1 values(1)
insert into t1 values(2)
insert into t1 values(3)
go
insert into t2 values(null)
insert into t2 values(2)
go
select *
from
t1
inner join
t2
on t1.c1 = t2.c1
or t2.c1 is null
go
drop table t1, t2
go
AMB
"Jens Sü?meyer" wrote:

> You mean that : ?
> SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Can" <Can@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:E19D9F15-A048-404F-9610-6826BF9E61D6@.microsoft.com...
>
>
|||Why not use a FOREIGN KEY to enforce referential integrity?
David Portas
SQL Server MVP
|||Good question!!!
AMB
"David Portas" wrote:

> Why not use a FOREIGN KEY to enforce referential integrity?
> --
> David Portas
> SQL Server MVP
> --
>
>

Accepting Null values in Trigger

The trigger below was automatically generated when I transferred my data fro
m
Access to SQL. Right now it needs to find a match between
Dressing.Dressing_ID and inserted.Day_Dressing_ID, that's great EXCEPT I wan
t
to put in an exception to say insert if Dressing.Dressing_ID =
inserted.Day_Dressing_ID or inserted.Day_Dressing_ID is null. I can't get
the syntax right. Any thoughts?
/* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
inserted.Day_Dressing_ID))
BEGIN
RAISERROR 44447 'The record can''t be added or changed. Referential
integrity rules require a related record in table ''Dressing''.'
ROLLBACK TRANSACTION
ENDYou mean that : ?
SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
> inserted.Day_Dressing_ID OR inserted.Day_Dressing_ID is null)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Can" <Can@.discussions.microsoft.com> schrieb im Newsbeitrag
news:E19D9F15-A048-404F-9610-6826BF9E61D6@.microsoft.com...
> The trigger below was automatically generated when I transferred my data
> from
> Access to SQL. Right now it needs to find a match between
> Dressing.Dressing_ID and inserted.Day_Dressing_ID, that's great EXCEPT I
> want
> to put in an exception to say insert if Dressing.Dressing_ID =
> inserted.Day_Dressing_ID or inserted.Day_Dressing_ID is null. I can't get
> the syntax right. Any thoughts?
> /* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
> IF (SELECT COUNT(*) FROM inserted) !=
> (SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
> inserted.Day_Dressing_ID))
> BEGIN
> RAISERROR 44447 'The record can''t be added or changed. Referential
> integrity rules require a related record in table ''Dressing''.'
> ROLLBACK TRANSACTION
> END
>|||Try,
/* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
IF (SELECT COUNT(*) FROM inserted where Day_Dressing_ID is not null) !=
(SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
inserted.Day_Dressing_ID))
BEGIN
RAISERROR 44447 'The record can''t be added or changed. Referential
integrity rules require a related record in table ''Dressing''.'
ROLLBACK TRANSACTION
END
AMB
"Can" wrote:

> The trigger below was automatically generated when I transferred my data f
rom
> Access to SQL. Right now it needs to find a match between
> Dressing.Dressing_ID and inserted.Day_Dressing_ID, that's great EXCEPT I w
ant
> to put in an exception to say insert if Dressing.Dressing_ID =
> inserted.Day_Dressing_ID or inserted.Day_Dressing_ID is null. I can't get
> the syntax right. Any thoughts?
> /* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
> IF (SELECT COUNT(*) FROM inserted) !=
> (SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
> inserted.Day_Dressing_ID))
> BEGIN
> RAISERROR 44447 'The record can''t be added or changed. Referentia
l
> integrity rules require a related record in table ''Dressing''.'
> ROLLBACK TRANSACTION
> END
>|||Jens,

This condition is saying to join a row from table Dressing to a row in table
inserted if the column Dressing_ID are equal in both tables or
inserted.Day_Dressing_ID is null, so if Dressing.Dressing_ID = 1 and
inserted.Dressing_ID is null those rows are joined.
This will not give you what you expect, see an example.
use northwind
go
create table t1 (
c1 int unique
)
go
create table t2 (
c1 int null
)
go
insert into t1 values(1)
insert into t1 values(2)
insert into t1 values(3)
go
insert into t2 values(null)
insert into t2 values(2)
go
select *
from
t1
inner join
t2
on t1.c1 = t2.c1
or t2.c1 is null
go
drop table t1, t2
go
AMB
"Jens Sü?meyer" wrote:
[vbcol=seagreen]
> You mean that : ?
> SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Can" <Can@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:E19D9F15-A048-404F-9610-6826BF9E61D6@.microsoft.com...
>
>|||Why not use a FOREIGN KEY to enforce referential integrity?
David Portas
SQL Server MVP
--|||Good question!!!
AMB
"David Portas" wrote:

> Why not use a FOREIGN KEY to enforce referential integrity?
> --
> David Portas
> SQL Server MVP
> --
>
>

Accepting Null values in Trigger

The trigger below was automatically generated when I transferred my data from
Access to SQL. Right now it needs to find a match between
Dressing.Dressing_ID and inserted.Day_Dressing_ID, that's great EXCEPT I want
to put in an exception to say insert if Dressing.Dressing_ID = inserted.Day_Dressing_ID or inserted.Day_Dressing_ID is null. I can't get
the syntax right. Any thoughts?
/* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
IF (SELECT COUNT(*) FROM inserted) != (SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID = inserted.Day_Dressing_ID))
BEGIN
RAISERROR 44447 'The record can''t be added or changed. Referential
integrity rules require a related record in table ''Dressing''.'
ROLLBACK TRANSACTION
ENDYou mean that : ?
SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID => inserted.Day_Dressing_ID OR inserted.Day_Dressing_ID is null)
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Can" <Can@.discussions.microsoft.com> schrieb im Newsbeitrag
news:E19D9F15-A048-404F-9610-6826BF9E61D6@.microsoft.com...
> The trigger below was automatically generated when I transferred my data
> from
> Access to SQL. Right now it needs to find a match between
> Dressing.Dressing_ID and inserted.Day_Dressing_ID, that's great EXCEPT I
> want
> to put in an exception to say insert if Dressing.Dressing_ID => inserted.Day_Dressing_ID or inserted.Day_Dressing_ID is null. I can't get
> the syntax right. Any thoughts?
> /* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
> IF (SELECT COUNT(*) FROM inserted) !=> (SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID => inserted.Day_Dressing_ID))
> BEGIN
> RAISERROR 44447 'The record can''t be added or changed. Referential
> integrity rules require a related record in table ''Dressing''.'
> ROLLBACK TRANSACTION
> END
>|||Try,
/* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
IF (SELECT COUNT(*) FROM inserted where Day_Dressing_ID is not null) != (SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =inserted.Day_Dressing_ID))
BEGIN
RAISERROR 44447 'The record can''t be added or changed. Referential
integrity rules require a related record in table ''Dressing''.'
ROLLBACK TRANSACTION
END
AMB
"Can" wrote:
> The trigger below was automatically generated when I transferred my data from
> Access to SQL. Right now it needs to find a match between
> Dressing.Dressing_ID and inserted.Day_Dressing_ID, that's great EXCEPT I want
> to put in an exception to say insert if Dressing.Dressing_ID => inserted.Day_Dressing_ID or inserted.Day_Dressing_ID is null. I can't get
> the syntax right. Any thoughts?
> /* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
> IF (SELECT COUNT(*) FROM inserted) !=> (SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID => inserted.Day_Dressing_ID))
> BEGIN
> RAISERROR 44447 'The record can''t be added or changed. Referential
> integrity rules require a related record in table ''Dressing''.'
> ROLLBACK TRANSACTION
> END
>|||Jens,
> > inserted.Day_Dressing_ID OR inserted.Day_Dressing_ID is null)
This condition is saying to join a row from table Dressing to a row in table
inserted if the column Dressing_ID are equal in both tables or
inserted.Day_Dressing_ID is null, so if Dressing.Dressing_ID = 1 and
inserted.Dressing_ID is null those rows are joined.
This will not give you what you expect, see an example.
use northwind
go
create table t1 (
c1 int unique
)
go
create table t2 (
c1 int null
)
go
insert into t1 values(1)
insert into t1 values(2)
insert into t1 values(3)
go
insert into t2 values(null)
insert into t2 values(2)
go
select *
from
t1
inner join
t2
on t1.c1 = t2.c1
or t2.c1 is null
go
drop table t1, t2
go
AMB
"Jens Sü�meyer" wrote:
> You mean that : ?
> SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID => > inserted.Day_Dressing_ID OR inserted.Day_Dressing_ID is null)
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Can" <Can@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:E19D9F15-A048-404F-9610-6826BF9E61D6@.microsoft.com...
> > The trigger below was automatically generated when I transferred my data
> > from
> > Access to SQL. Right now it needs to find a match between
> > Dressing.Dressing_ID and inserted.Day_Dressing_ID, that's great EXCEPT I
> > want
> > to put in an exception to say insert if Dressing.Dressing_ID => > inserted.Day_Dressing_ID or inserted.Day_Dressing_ID is null. I can't get
> > the syntax right. Any thoughts?
> >
> > /* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
> > IF (SELECT COUNT(*) FROM inserted) !=> > (SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID => > inserted.Day_Dressing_ID))
> > BEGIN
> > RAISERROR 44447 'The record can''t be added or changed. Referential
> > integrity rules require a related record in table ''Dressing''.'
> > ROLLBACK TRANSACTION
> > END
> >
> >
>
>|||Why not use a FOREIGN KEY to enforce referential integrity?
--
David Portas
SQL Server MVP
--|||Good question!!!
AMB
"David Portas" wrote:
> Why not use a FOREIGN KEY to enforce referential integrity?
> --
> David Portas
> SQL Server MVP
> --
>
>

Absurdal affair in trigger file

Hi!

I found a bug in my trigger and i don't know why it is error.

See small report:

After executing following code's fragment in my trigger:

print '************** koszt sadowe *****************' + str(@.orzecz_koszty_sadowe)
print '************** koszt mks *****************' + str(@.orzecz_mks_przyznane)
print '************** koszt korespondencji *****************' + str(@.orzecz_zasadzona_zaliczka_na_koresp)

-- Potrzebne do wstawienia pozycji 'Koszty sadowe'
IF @.orzecz_koszty_sadowe IS NULL
SET @.orzecz_koszty_sadowe = 0
IF @.orzecz_mks_przyznane IS NULL
SET @.orzecz_mks_przyznane = 0

IF @.orzecz_oplaty_pozostale IS NULL
SET @.orzecz_oplaty_pozostale = 0

IF @.orzecz_zasadzona_zaliczka_na_koresp IS NULL
SET @.orzecz_zasadzona_zaliczka_na_koresp = 0

-- Koniec sprawdzanie czy sa NULL

print '************** koszt sadowe *****************' + str(@.orzecz_koszty_sadowe)
print '************** koszt mks *****************' + str(@.orzecz_mks_przyznane)
print '************** koszt korespondencji *****************' + str(@.orzecz_zasadzona_zaliczka_na_koresp)

we have on consolle following result:

************** koszt sadowe ***************** 145
************** koszt mks ***************** 123
************** koszt korespondencji ***************** 43
************** koszt sadowe ***************** 0
************** koszt mks ***************** 123
************** koszt korespondencji ***************** 43

So, variable @.orzecz_koszty_sadowe was set to 0.
Previously it was 145, so why in the if statement it values to NULL?

After small change, code looks like this:

print '************** koszt sadowe *****************' + str(@.orzecz_koszty_sadowe)
print '************** koszt mks *****************' + str(@.orzecz_mks_przyznane)
print '************** koszt korespondencji *****************' + str(@.orzecz_zasadzona_zaliczka_na_koresp)

-- Potrzebne do wstawienia pozycji 'Koszty sadowe'
IF @.orzecz_koszty_sadowe IS NULL
SET @.orzecz_koszty_sadowe = 0
IF @.orzecz_mks_przyznane IS NULL
SET @.orzecz_mks_przyznane = 0

IF @.orzecz_oplaty_pozostale IS NULL
SET @.orzecz_oplaty_pozostale = 0

IF @.orzecz_zasadzona_zaliczka_na_koresp IS NULL
SET @.orzecz_zasadzona_zaliczka_na_koresp = 0

-- Koniec sprawdzanie czy sa NULL

print '************** koszt sadowe *****************' + str(@.orzecz_koszty_sadowe)
print '************** koszt mks *****************' + str(@.orzecz_mks_przyznane)
print '************** koszt korespondencji *****************' + str(@.orzecz_zasadzona_zaliczka_na_koresp)

and the result is following:

************** koszt sadowe ***************** 145
************** koszt mks ***************** 123
************** koszt korespondencji ***************** 43
************** koszt sadowe ***************** 145
************** koszt mks ***************** 123
************** koszt korespondencji ***************** 43

So the result is proper. Now I explain the difference between
above listings. Second listing arise from first by following
transformation:

1. Place the cursor after last character of comment preceding
first IF statement (ie. after ....pozycji 'Koszty sadowe')
2. Press Enter key
3. Press Delete key (the line comes back, to initial position)

And that's all :) There's no optical difference between two
listings. However first works bad, and the second works good.

Any idea? Explanation?
I used SQL Server 2000.
Best regards
Walter

Could you please post a script that demonstrates the problem?|||Probably there was a Unix-style newline in your SQL. The query editor displays CHR(13) without CHR(10) as a new line, but the SQL processor considers the text before and after CHR(13) to be part of the same line if there is no CHAR(10).

So the SQL processor interpreted
-- Potrzebne do wstawienia pozycji 'Koszty sadowe'
IF @.orzecz_koszty_sadowe IS NULL

to mean the same thing as

-- Potrzebne do wstawienia pozycji 'Koszty sadowe' IF @.orzecz_koszty_sadowe IS NULL

The typing you did changed the CHR(13) to a valid 2-character Windows newline marker.

Steve Kass
Drew University

About Triggers

Hi..I'm still new to topics about triggers..

The scenario is like this:

I want my trigger to copy the newly added records from our ERP table...The ERP table contains all records for materials receiving and returning, sales orders, and purchases....I want my trigger to only copy those newly added records for materials receiving and returning...After studying the table, I managed to get the keys on how to get only the records for materials receiving and returning...So my trigger goes like this

CREATE TRIGGER INSERT_TO_DUMMY ON [dbo].[gbkmut]
FOR INSERT
AS

DECLARE @.ID INT,
@.DATUM DATETIME,
@.SUPCODE CHAR(12),
@.ITEMCODE CHAR(30),
@.QTY FLOAT(8),
@.PONUM CHAR(20),
@.UNIT CHAR(8),
@.JENTRY CHAR(9),
@.LINK UNIQUEIDENTIFIER,
@.TYPE CHAR,
@.SUBTYPE CHAR

SELECT @.ID = ID,
@.DATUM = DATUM,
@.SUPCODE = CRDNR,
@.ITEMCODE = ARTCODE,
@.QTY = AANTAL,
@.PONUM = BKSTNR_SUB,
@.UNIT = UNITCODE,
@.JENTRY = REKNR,
@.LINK = LINKEDLINE,
@.TYPE = OORSPRONG,
@.SUBTYPE = TRANSSUBTYPE
FROM GBKMUT

IF (@.PONUM IS NOT NULL)
BEGIN
IF (@.JENTRY <> 2140) the
BEGIN
IF (((@.TYPE = 'R') AND (@.SUBTYPE <> 'J') AND (@.LINK IS NOT NULL)) OR ((@.TYPE = 'U') AND (@.SUBTYPE = 'J') AND (@.LINK IS NULL)))
BEGIN
INSERT INTO I_DUMMY VALUES(@.ID, @.DATUM, @.SUPCODE, @.ITEMCODE, @.QTY, @.PONUM, @.UNIT, @.JENTRY, @.LINK, @.TYPE, @.SUBTYPE,'I')
END
END
END

My problem is that the I_DUMMY table does not copy the newly added record...It copies a single record and stores it about 38 times....What would seem to be the problem in my code?

I have created this code by doing right-click on the GBKMUT table then click in to Manage Triggers in the SQL Server Enterprise Manager.

Hope anyone could help me on this...If you have further questions regarding my inquiry, feel free to say so...

Thanks a lot...

A common mistake for those new to working with TRIGGERs is trying (or assuming) that the data must be handled one row at at time.

In a TRIGGER, there are two virtual tables, one named inserted, and one named deleted. The inserted table has new rows for inserts, and new values for updates. The deleted table has rows that were deleted, and old valued for updates.

In your situation, it seems that you just want to add all rows from the inserted table into the BbkMut table, based upon certain criteria.

I also suggest a variation to your naming convention. If you name Triggers and Stored Procedures with the primary affected table as the first part of the name, they will be easier to find.

(This is untested, but should point you in the right direction.)

CREATE TRIGGER GbkMut_I_To_Dummy
ON dbo.GbkMut
FOR INSERT
AS

IF @.@.ROWCOUNT = 0
RETURN
INSERT INTO I_DUMMY
SELECT
ID,
DATUM,
CRDNR,
ARTCODE,
AANTAL,
BKSTNR_SUB,
UNITCODE,
REKNR,
LINKEDLINE,
OORSPRONG,
TRANSSUBTYPE,
'I')
FROM inserted i
WHERE ( BKSTNR_SUB IS NOT NULL
AND REKNR <> 2140
AND ( ( OORSPRONG = 'R'
AND TRANSSUBTYPE <> 'J'
AND LINKEDLINE IS NOT NULL
)
OR ( OORSPRONG = 'U'
AND TRANSSUBTYPE = 'J'
AND LINKEDLINE IS NULL
)
)
)

|||

Oh i see..So you mean I have to copy the newly added records from the virtual table inserted into my destination table I_Dummy....Thanks for that info...I really thought the trigger would automatically know what are the newly added fileds on my main table...Well on what I have understood from you, it will be stored on a virtual table...

By the way, a follow-up question regarding the virtual table...For example, my ERP table got a newly added record...Tha record will then be stored to the virtual table INSERTED...then my trigger would copy that record into the table I_Dummy....What if a new record will be added again? Will my trigger still copy the record it had already added plus the new record added again?

here's to illustrate my question

SCENARIO 1

MAIN TABLE

rec1

rec2

rec3

newrec1

INSERTED

newrec1

I_DUMMY

newrec1 -copied through trigger

SCENARIO2

MAIN TABLE

rec1

rec2

rec3

newrec1

newrec2

INSERTED

newrec1

newrec2 -will it be like this or newrec1 will automatically be deleted?

I_DUMMY

newrec1 -copied through trigger

newrec1 -will it be like this or only newrec2 will be copied?

newrec2

|||

When any process inserts a row into the table, the TRIGGER will 'fire'. At that moment, the inserted table is created and available to the TRIGGER and contains ONLY the newly inserted rows.

If another user inserts a row into the table at almost the same exact moment, the TRIGGER will fire (for that user) and the inserted table will be created and available to the TRIGGER, containing ONLY the rows inserted by this user.

Each user has a separate inserted table that contains ONLY THEIR inserts.

|||

To confirm what I have understood, suppose i have only one user, she inserted a new record...the INSERTED table will be created and would contain the newly inserted record (newrecord1)...based from my trigger's code, record1 will be copied to I_Dummy table...

If my user(the same user) would insert another record, the INSERTED table for that user would now contain record1 and record2...But how about my I_Dummy table? Will it still copy record1? or just record2?

|||

You have almost got the idea.

Each time the TRIGGER fires (On each separate INSERT) a new inserted table is created, containing ONLY the rows from that specific INSERT. It does not contain previous rows from previous inserts by the same user. It does not contain rows from inserts from other users. It does not contain rows from a previous INSERT at all.

It ONLY contains the rows that were inserted and caused the TRIGGER to fire.

|||

so now I get it...thanks for your help...i'm going to try the code you have given me, i'll just edit some stuffs in there, then i'll write feedback in here after testing it...thanks a lot again

|||hi arnie...still the code does not copy the right record...i'll still try to work on my triggers...thanks for all of the information you provided....

About Triggers

Hi..I'm still new to topics about triggers..

The scenario is like this:

I want my trigger to copy the newly added records from our ERP table...The ERP table contains all records for materials receiving and returning, sales orders, and purchases....I want my trigger to only copy those newly added records for materials receiving and returning...After studying the table, I managed to get the keys on how to get only the records for materials receiving and returning...So my trigger goes like this

CREATE TRIGGER INSERT_TO_DUMMY ON [dbo].[gbkmut]
FOR INSERT
AS

DECLARE @.ID INT,
@.DATUM DATETIME,
@.SUPCODE CHAR(12),
@.ITEMCODE CHAR(30),
@.QTY FLOAT(8),
@.PONUM CHAR(20),
@.UNIT CHAR(8),
@.JENTRY CHAR(9),
@.LINK UNIQUEIDENTIFIER,
@.TYPE CHAR,
@.SUBTYPE CHAR

SELECT @.ID = ID,
@.DATUM = DATUM,
@.SUPCODE = CRDNR,
@.ITEMCODE = ARTCODE,
@.QTY = AANTAL,
@.PONUM = BKSTNR_SUB,
@.UNIT = UNITCODE,
@.JENTRY = REKNR,
@.LINK = LINKEDLINE,
@.TYPE = OORSPRONG,
@.SUBTYPE = TRANSSUBTYPE
FROM GBKMUT

IF (@.PONUM IS NOT NULL)
BEGIN
IF (@.JENTRY <> 2140) the
BEGIN
IF (((@.TYPE = 'R') AND (@.SUBTYPE <> 'J') AND (@.LINK IS NOT NULL)) OR ((@.TYPE = 'U') AND (@.SUBTYPE = 'J') AND (@.LINK IS NULL)))
BEGIN
INSERT INTO I_DUMMY VALUES(@.ID, @.DATUM, @.SUPCODE, @.ITEMCODE, @.QTY, @.PONUM, @.UNIT, @.JENTRY, @.LINK, @.TYPE, @.SUBTYPE,'I')
END
END
END

My problem is that the I_DUMMY table does not copy the newly added record...It copies a single record and stores it about 38 times....What would seem to be the problem in my code?

I have created this code by doing right-click on the GBKMUT table then click in to Manage Triggers in the SQL Server Enterprise Manager.

Hope anyone could help me on this...If you have further questions regarding my inquiry, feel free to say so...

Thanks a lot...

A common mistake for those new to working with TRIGGERs is trying (or assuming) that the data must be handled one row at at time.

In a TRIGGER, there are two virtual tables, one named inserted, and one named deleted. The inserted table has new rows for inserts, and new values for updates. The deleted table has rows that were deleted, and old valued for updates.

In your situation, it seems that you just want to add all rows from the inserted table into the BbkMut table, based upon certain criteria.

I also suggest a variation to your naming convention. If you name Triggers and Stored Procedures with the primary affected table as the first part of the name, they will be easier to find.

(This is untested, but should point you in the right direction.)

CREATE TRIGGER GbkMut_I_To_Dummy
ON dbo.GbkMut
FOR INSERT
AS

IF @.@.ROWCOUNT = 0
RETURN
INSERT INTO I_DUMMY
SELECT
ID,
DATUM,
CRDNR,
ARTCODE,
AANTAL,
BKSTNR_SUB,
UNITCODE,
REKNR,
LINKEDLINE,
OORSPRONG,
TRANSSUBTYPE,
'I')
FROM inserted i
WHERE ( BKSTNR_SUB IS NOT NULL
AND REKNR <> 2140
AND ( ( OORSPRONG = 'R'
AND TRANSSUBTYPE <> 'J'
AND LINKEDLINE IS NOT NULL
)
OR ( OORSPRONG = 'U'
AND TRANSSUBTYPE = 'J'
AND LINKEDLINE IS NULL
)
)
)

|||

Oh i see..So you mean I have to copy the newly added records from the virtual table inserted into my destination table I_Dummy....Thanks for that info...I really thought the trigger would automatically know what are the newly added fileds on my main table...Well on what I have understood from you, it will be stored on a virtual table...

By the way, a follow-up question regarding the virtual table...For example, my ERP table got a newly added record...Tha record will then be stored to the virtual table INSERTED...then my trigger would copy that record into the table I_Dummy....What if a new record will be added again? Will my trigger still copy the record it had already added plus the new record added again?

here's to illustrate my question

SCENARIO 1

MAIN TABLE

rec1

rec2

rec3

newrec1

INSERTED

newrec1

I_DUMMY

newrec1 -copied through trigger

SCENARIO2

MAIN TABLE

rec1

rec2

rec3

newrec1

newrec2

INSERTED

newrec1

newrec2 -will it be like this or newrec1 will automatically be deleted?

I_DUMMY

newrec1 -copied through trigger

newrec1 -will it be like this or only newrec2 will be copied?

newrec2

|||

When any process inserts a row into the table, the TRIGGER will 'fire'. At that moment, the inserted table is created and available to the TRIGGER and contains ONLY the newly inserted rows.

If another user inserts a row into the table at almost the same exact moment, the TRIGGER will fire (for that user) and the inserted table will be created and available to the TRIGGER, containing ONLY the rows inserted by this user.

Each user has a separate inserted table that contains ONLY THEIR inserts.

|||

To confirm what I have understood, suppose i have only one user, she inserted a new record...the INSERTED table will be created and would contain the newly inserted record (newrecord1)...based from my trigger's code, record1 will be copied to I_Dummy table...

If my user(the same user) would insert another record, the INSERTED table for that user would now contain record1 and record2...But how about my I_Dummy table? Will it still copy record1? or just record2?

|||

You have almost got the idea.

Each time the TRIGGER fires (On each separate INSERT) a new inserted table is created, containing ONLY the rows from that specific INSERT. It does not contain previous rows from previous inserts by the same user. It does not contain rows from inserts from other users. It does not contain rows from a previous INSERT at all.

It ONLY contains the rows that were inserted and caused the TRIGGER to fire.

|||

so now I get it...thanks for your help...i'm going to try the code you have given me, i'll just edit some stuffs in there, then i'll write feedback in here after testing it...thanks a lot again

|||hi arnie...still the code does not copy the right record...i'll still try to work on my triggers...thanks for all of the information you provided....

about trigger ..

what's the problem?
create trigger T_credito
after insert on movimento
declare
periodo number;
begin
select durata into periodo from movimento
where codcliente=:new.codcliente and codfilm=:new.codfilm and codnegozio=new.codnegozio;
if periodo = 0
then
update CLIENTE
set credito=credito - 2
where user_id= :new.codcliente;
else
update CLIENTE
set credito=credito - (periodo *3)
where user_id= :new.codcliente;
end;
thank you ElisaHi,

This is the problem:

where codcliente=:new.codcliente and codfilm=:new.codfilm
and codnegozio=new.codnegozio;

In an after insert trigger you no longer have access to the :new and :old variables.

;)|||Originally posted by LKBrwn_DBA
Hi,

This is the problem:

where codcliente=:new.codcliente and codfilm=:new.codfilm
and codnegozio=new.codnegozio;

In an after insert trigger you no longer have access to the :new and :old variables.

;)

I understand but what I can need? It's very important|||You will need to create one package and THREE triggers, something like this:

Create Package Credito_Pkg
As
Type Codcliente_Typ Is Table Of Movimento.Codcliente%Type;
Type Codfilm_Typ Is Table Of Movimento.Codfilm%Type;
Type Codnegozio_Typ Is Table Of Movimento.Codnegozio%Type;
PV_Codcliente Codcliente_Typ;
PV_Codfilm Codfilm_Typ;
PV_Codnegozio Codnegozio_Typ;
Cnt_Cte Pls_Integer;
End;
/
Create Trigger Tb_Credito
Before Insert On Movimento
Begin
Credito_Pkg.Cnt_Cte := 0;
Credito_Pkg.PV_Codcliente.Delete;
Credito_Pkg.PV_Codfilm.Delete;
Credito_Pkg.PV_Codnegozio.Delete;
End;
/
Create Trigger Tar_Credito
After Insert On Movimento For Each Row
Begin
Credito_Pkg.Cnt_Cte := Credito_Pkg.Cnt_Cte + 1;
Credito_Pkg.PV_Codcliente(Credito_Pkg.Cnt_Cte) := :New.Codcliente;
Credito_Pkg.PV_Codfilm(Credito_Pkg.Cnt_Cte) := :New.Codfilm;
Credito_Pkg.PV_Codnegozio(Credito_Pkg.Cnt_Cte) := :New.Codnegozio;
End;
/
Create Trigger Ta_Credito
After Insert On Movimento
Declare
Periodo Number;
P Pls_Integer;
Begin
For P In 1..Credito_Pkg.Cnt_Cte Loop
Select Durata
Into Periodo
From Movimento
Where Codcliente = Credito_Pkg.PV_Codcliente(P)
And Codfilm = Credito_Pkg.PV_Codfilm(P)
And Codnegozio = Credito_Pkg.PV_Codnegozio(P);
If Periodo = 0
Then
Update Cliente
Set Credito = Credito - 2
Where User_Id=Credito_Pkg.PV_Codcliente(P);
Else
Update Cliente
Set Credito = Credito - (Periodo *3)
Where User_Id=Credito_Pkg.PV_Codcliente(P);
End If;
End Loop;
End;
/

:cool:

NOTE: Is not optimized...|||Originally posted by LKBrwn_DBA
You will need to create one package and THREE triggers, something like this:

Create Package Credito_Pkg
As
Type Codcliente_Typ Is Table Of Movimento.Codcliente%Type;
Type Codfilm_Typ Is Table Of Movimento.Codfilm%Type;
Type Codnegozio_Typ Is Table Of Movimento.Codnegozio%Type;
PV_Codcliente Codcliente_Typ;
PV_Codfilm Codfilm_Typ;
PV_Codnegozio Codnegozio_Typ;
Cnt_Cte Pls_Integer;
End;
/
Create Trigger Tb_Credito
Before Insert On Movimento
Begin
Credito_Pkg.Cnt_Cte := 0;
Credito_Pkg.PV_Codcliente.Delete;
Credito_Pkg.PV_Codfilm.Delete;
Credito_Pkg.PV_Codnegozio.Delete;
End;
/
Create Trigger Tar_Credito
After Insert On Movimento For Each Row
Begin
Credito_Pkg.Cnt_Cte := Credito_Pkg.Cnt_Cte + 1;
Credito_Pkg.PV_Codcliente(Credito_Pkg.Cnt_Cte) := :New.Codcliente;
Credito_Pkg.PV_Codfilm(Credito_Pkg.Cnt_Cte) := :New.Codfilm;
Credito_Pkg.PV_Codnegozio(Credito_Pkg.Cnt_Cte) := :New.Codnegozio;
End;
/
Create Trigger Ta_Credito
After Insert On Movimento
Declare
Periodo Number;
P Pls_Integer;
Begin
For P In 1..Credito_Pkg.Cnt_Cte Loop
Select Durata
Into Periodo
From Movimento
Where Codcliente = Credito_Pkg.PV_Codcliente(P)
And Codfilm = Credito_Pkg.PV_Codfilm(P)
And Codnegozio = Credito_Pkg.PV_Codnegozio(P);
If Periodo = 0
Then
Update Cliente
Set Credito = Credito - 2
Where User_Id=Credito_Pkg.PV_Codcliente(P);
Else
Update Cliente
Set Credito = Credito - (Periodo *3)
Where User_Id=Credito_Pkg.PV_Codcliente(P);
End If;
End Loop;
End;
/

:cool:

NOTE: Is not optimized...

This line of code isn't exactly because I have an error and I don't know what I can do.
The error is:
Error: ORA-06531: Reference to uninitialized collection
ORA-06512: at "GL15.TB_CREDITO", line 3
ORA-04088: error during execution of trigger 'GL15.TB_CREDITO'

Thank you for your help. Elisa|||True,

Change these lines:

Credito_Pkg.PV_Codcliente.Delete;
Credito_Pkg.PV_Codfilm.Delete;
Credito_Pkg.PV_Codnegozio.Delete;

To this:

Credito_Pkg.PV_Codcliente := Credito_Pkg.Codcliente_Typ();
Credito_Pkg.PV_Codfilm := Credito_Pkg.Codfilm_Typ();
Credito_Pkg.PV_Codnegozio := Credito_Pkg.Codnegozio_Typ();

;)|||I know I'm boring you but with the new line there is a new error:

Error: ORA-06533: Subscript beyond count
ORA-06512: at "GL15.TAR_CREDITO", line 3
ORA-04088: error during execution of trigger 'GL15.TAR_CREDITO'

and I don't understand what's the problem.
If you can help me I'm very happy.
Thank you,
Elisa|||Since the trigger does update operations you will be able to make the trigger a before insert. If the insert fails by any chance there is no problem since the effect of the trigger is also reversed.|||I don't understand .. I'm afraid .. can you explain another time.
Thank you|||Elisa,

Pls change this:

Create Package Credito_Pkg
As
Type Codcliente_Typ Is Table Of Movimento.Codcliente%Type;
Type Codfilm_Typ Is Table Of Movimento.Codfilm%Type;
Type Codnegozio_Typ Is Table Of Movimento.Codnegozio%Type;
...etc...

To This:

Create Package Credito_Pkg
As
Type Codcliente_Typ Is Table Of Movimento.Codcliente%Type Index By Binary_Integer;
Type Codfilm_Typ Is Table Of Movimento.Codfilm%Type Index By Binary_Integer;
Type Codnegozio_Typ Is Table Of
Movimento.Codnegozio%Type Index By Binary_Integer;
...etc...

:D|||Thank you very much, Elisa

About trigger

is it possible to run a trigger forever... whose work is to archive data from a table. or how can i do the operation to archive automatically ?

Regards,

Kamrul Hassan

Well... triggers cannot be used for schedule tasking, there are certain some ways to do this.
1st) if you are running on dedicated server then you can use windows service to update records periodically
2nd) Use Sql Server jobs. to know more about sql server jobs http://msdn2.microsoft.com/en-us/library/ms187880.aspx

cheers..

About PL\SQL Trigger...

Hi all, I just wondering, when we should use Trigger statement and Trigger row? Because I'm not really understand in this part.
emm, where I can get complete free tutorial about this SQL Trigger?
Thanks in advance.the difference is when you insert more row in a time (tipically with a insert into ... select..)
Trigger statement is tipically faster, because is called one time, trigger row are tipically more easy to write because you have to manage only a row a time.
Trigger statement can be usefull also for checking a complex check that involve a set of row as a whole (for example chaecking the sum of a column must to be zero...)
pay attention that not every rdbm has both: for example, MS SQL has only the trigger statement, sqlite "for each row " only...|||You mention PL/SQL, so you are talking about Oracle triggers. See the free online documentation here:

Application Developer's Guide (http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg13trg.htm#376)

Concepts (http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c18trigs.htm#12312)|||Regarding your first question:

Statement-level triggers fire once only per statement, and cannot refer to specific row values using :OLD and :NEW
Row-level triggers fire once per row affected by the statement, and can refer to specific row values using :OLD and :NEW