a basic trigger

  • 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

  • Post the structure of your table, just in case and to write the trigger properly!

    Don't forget some sample data!

    :hehe::hehe::hehe:

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • 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' .

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Try this:

    CREATE TRIGGER TEST

    ON CUSTOMER

    AFTER INSERT

    AS

    INSERT INTO articles

    SELECT OCUSTOMERNR, 0 FROM INSERTED

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • 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

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply