Insert Trigger for only some records

[HubbleO]

I currently have a table with many fields and want to add a record (with only some data) to a second table when a record is added to the first.

When a record is added to the first table if the DOCNUMBR field of the record added starts with 'INV' I want to add a record to my second table. Fields in the second table are DOCNUMBR and CUSTNUMBR (which are in the first table) and the date/time when the record is written into the second table.

 

[Crowley16]

use a where clause in your trigger

--------------------
Procrastinate Now!

 

[SQLSister]

What do you have so far?

"NOTHING is more important in a database than integrity." ESquared

 

[HubbleO]

CREATE TRIGGER tr_InvoiceHistoried_INSERT

ON RM30101

FOR INSERT

AS

INSERT INTO stmClosedInvoices

(CustomerNumber, DocNumber, Stamp)

SELECT CUSTNMBR, DOCNUMBR, { fn NOW() } AS Expr1
FROM inserted
WHERE (LEFT(DOCNUMBR, 3) = 'INV')

 

[bborissov]

And what is wrong with it?
BTW why not use default value for Stamp to be GETDATE() and change this:

...
INSERT INTO stmClosedInvoices  (CustomerNumber, DocNumber)
SELECT CUSTNMBR, DOCNUMBR
FROM  Inserted
WHERE (LEFT(DOCNUMBR, 3) = 'INV')
...

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[HubbleO]

>>And what is wrong with it?
BTW why not use default value for Stamp to be GETDATE() and change this:<<

Actually it looks like it does work. I guess I was just second guessing myself since I'm not really that familair with SQL Server, I work in VBA in Access (and will be accessing these tables from Access).

By using the default value you mean in the design of the table set the default value property for the 'Stamp' field to 'GETDATE()', correct? Would that give me the date and time (e.g '3/5/2008 3:13:10 PM')? I assuming doing that would be quicker than including it in the trigger code?

 

[bborissov]

Yes.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[SQLSister]

In general if you can do something with a constraint or a default, it is better for perfomance that doing the same thing in a trigger.

"NOTHING is more important in a database than integrity." ESquared