June 12, 2009 at 5:33 am
Dear All,
i have two tables:
- articles
- customers
i want to write a trigger: when inserting a new record to the table 'customers', a new record most be added into the 'articles' too. the new record in the 'articles' most have the new added 'customernr' and '0' .
Thanks
June 12, 2009 at 5:51 am
Post the structure of your table, just in case and to write the trigger properly!
Don't forget some sample data!
:hehe::hehe::hehe:
June 12, 2009 at 7:19 am
Hi Dugi,
Thanks for your response.
These are the tables:
table customers has two columns:
CREATE TABLE [dbo].[customer](
[ocustomernr] [int] NOT NULL,
[onaam] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_customer] PRIMARY KEY CLUSTERED
(
[ocustomernr] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
table articles has two columns too:
CREATE TABLE [dbo].[articles](
[ocustomernr] [int] NULL,
[oaantal] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[articles] WITH CHECK ADD CONSTRAINT [FK_articles_customer] FOREIGN KEY([customernr])
REFERENCES [dbo].[customer] ([ocustomernr])
GO
ALTER TABLE [dbo].[articles] CHECK CONSTRAINT [FK_articles_customer]
Now i want that when inserting a new record into the customer table, a new record most be added into the articles too. in the 'customernr' column, the customernr and in the column 'oaatan' a '0' .
June 12, 2009 at 7:30 am
excellent. with the table definitions it is REALLY easy for us to help. good job providing us with that.
here's a basic example that works; note the key is to use the virtual table INSERTED (or DELETED if you were doing updates or deletes) to get just the rows that were affected by the INSERT statement.
If you didn't know, these virtual tables have the same columns that the table has that the trigger is defined on, and have the "new" values (for insert and update) and the old values (for update or delete) for the table transaction that is occurring.
These virtual tables only exist inside the trigger, for the duration of the trigger.
CREATE TRIGGER TR_CUSTOMER ON CUSTOMER
FOR INSERT
AS
BEGIN
INSERT INTO [articles]([ocustomernr],[oaantal])
SELECT [ocustomernr],0 FROM INSERTED
END
GO
--now prove it works for multi-row insert:
INSERT INTO [customer]([ocustomernr],[onaam])
SELECT 1,'First Customer' UNION ALL
SELECT 2,'Second Customer' UNION ALL
SELECT 3,'Third Customer' UNION ALL
SELECT 4,'Fourth Customer'
select * from [customer]
select * from [articles]
--results:
ocustomernroaantal
4 0
3 0
2 0
1 0
Lowell
June 12, 2009 at 7:36 am
June 12, 2009 at 7:38 am
kandevs (6/12/2009)
Hi Dugi,Thanks for your response.
These are the tables:
table customers has two columns:
CREATE TABLE [dbo].[customer](
[ocustomernr] [int] NOT NULL,
[onaam] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_customer] PRIMARY KEY CLUSTERED
(
[ocustomernr] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
table articles has two columns too:
CREATE TABLE [dbo].[articles](
[ocustomernr] [int] NULL,
[oaantal] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[articles] WITH CHECK ADD CONSTRAINT [FK_articles_customer] FOREIGN KEY([customernr])
REFERENCES [dbo].[customer] ([ocustomernr])
GO
ALTER TABLE [dbo].[articles] CHECK CONSTRAINT [FK_articles_customer]
Now i want that when inserting a new record into the customer table, a new record most be added into the articles too. in the 'customernr' column, the customernr and in the column 'oaatan' a '0' .
Try this:
CREATE TRIGGER TEST
ON CUSTOMER
AFTER INSERT
AS
INSERT INTO articles
SELECT OCUSTOMERNR, 0 FROM INSERTED
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply