Hi

I am new to triggers. I have checked the syntax of triggers but it is very
complicated for a newbie like me. What is the syntax of a trigger that will
once a new record is inserted into the Contacts table, sets the value of the
column 'Company Type' to Client in the inserted record?

Thanks

Regards

You'll want something like this.

CREATE TRIGGER t_Contacts_i ON Contacts
FOR INSERTED
AS
UPDATE Contacts
set [Company Type] = 'Client'
FROM inserted
WHERE Contacts.ContactId = inserted.ContactId
GO

What we do here is use the virtual table inserted which contains all
the new records which were just inserted into the table to filter the
records on the table so that we can find the correct record(s).

You'll need to change ContactId to what ever the primary key is for
the Contacts table.

Denny

On Thu, 17 Jul 2008 18:29:29 +0100, "John" <info@nospam.infovis.co.uk>
wrote:

>Hi
>
>I am new to triggers. I have checked the syntax of triggers but it is very
>complicated for a newbie like me. What is the syntax of a trigger that will
>once a new record is inserted into the Contacts table, sets the value of the
>column 'Company Type' to Client in the inserted record?
>
>Thanks
>
>Regards
>

Hi Denny

Many thanks. This has made it very clear.

Regards

"Denny Cherry" <mrdenny.nospam@mrdenny.com> wrote in message
news:6u0v74trhbucv09cq200c5u8g1jc2eed4h@4ax.com...
> You'll want something like this.
>
> CREATE TRIGGER t_Contacts_i ON Contacts
> FOR INSERTED
> AS
> UPDATE Contacts
> set [Company Type] = 'Client'
> FROM inserted
> WHERE Contacts.ContactId = inserted.ContactId
> GO
>
> What we do here is use the virtual table inserted which contains all
> the new records which were just inserted into the table to filter the
> records on the table so that we can find the correct record(s).
>
> You'll need to change ContactId to what ever the primary key is for
> the Contacts table.
>
> Denny
>
> On Thu, 17 Jul 2008 18:29:29 +0100, "John" <info@nospam.infovis.co.uk>
> wrote:
>
>>Hi
>>
>>I am new to triggers. I have checked the syntax of triggers but it is very
>>complicated for a newbie like me. What is the syntax of a trigger that
>>will
>>once a new record is inserted into the Contacts table, sets the value of
>>the
>>column 'Company Type' to Client in the inserted record?
>>
>>Thanks
>>
>>Regards
>>

If this is something fixed, I mean if every inserted record's "Company Type"
value will be "Client" then why do you intend to use Triggers?

You can make your application insert this value programmatically. Or you can
use DEFAULT constraint for "Company Type" field.

Will you tell us more about this INSERT operation? Then we may suggest
something more practical.

--
Ekrem Önsoy



"John" <info@nospam.infovis.co.uk> wrote in message
news:uaaeqKD6IHA.1468@TK2MSFTNGP05.phx.gbl...
> Hi
>
> I am new to triggers. I have checked the syntax of triggers but it is very
> complicated for a newbie like me. What is the syntax of a trigger that
> will once a new record is inserted into the Contacts table, sets the value
> of the column 'Company Type' to Client in the inserted record?
>
> Thanks
>
> Regards
>
>

Hi Ekrem

In one instance I need all 'Client' contacts (as specified by 'Company Type'
field) and when a new record is saved I want it saved as Client contact. In
another instance I want to view and save 'Supplier' contacts and so on.

The solution I am trying (being a newbie) is to cerate views Client
Contacts, Supplier Contacts and so on with each company type then to each
view add a trigger that would set the correct company type when a new record
is inserted.

If this is not correct or best strategy then please let me know.

Thanks

Regards

"Ekrem Önsoy" <ekrem@compecta.com> wrote in message
news38CE2B9-DBE7-450E-83B7-AEACCCF7B3B5@microsoft.com...
> If this is something fixed, I mean if every inserted record's "Company
> Type" value will be "Client" then why do you intend to use Triggers?
>
> You can make your application insert this value programmatically. Or you
> can use DEFAULT constraint for "Company Type" field.
>
> Will you tell us more about this INSERT operation? Then we may suggest
> something more practical.
>
> --
> Ekrem Önsoy
>
>
>
> "John" <info@nospam.infovis.co.uk> wrote in message
> news:uaaeqKD6IHA.1468@TK2MSFTNGP05.phx.gbl...
>> Hi
>>
>> I am new to triggers. I have checked the syntax of triggers but it is
>> very complicated for a newbie like me. What is the syntax of a trigger
>> that will once a new record is inserted into the Contacts table, sets the
>> value of the column 'Company Type' to Client in the inserted record?
>>
>> Thanks
>>
>> Regards
>>
>>
>

That is an interesting technique to use at the least. I applaud your
idea.

A better solution would be to use a stored procedure to handle your
inserts, that way you can put logic within the stored procedure to
figure out which value needs to be put in, or simply pass in the value
as one of the parameters and put it strait into the table.

I wrote a blog entry about Stored Procedures
(http://itknowledgeexchange.techtarge...-the-database/)
as part of my Back to Basics series
(http://itknowledgeexchange.techtarge...ack-to-basics/).

Denny

On Thu, 17 Jul 2008 19:07:35 +0100, "John" <info@nospam.infovis.co.uk>
wrote:

>Hi Ekrem
>
>In one instance I need all 'Client' contacts (as specified by 'Company Type'
>field) and when a new record is saved I want it saved as Client contact. In
>another instance I want to view and save 'Supplier' contacts and so on.
>
>The solution I am trying (being a newbie) is to cerate views Client
>Contacts, Supplier Contacts and so on with each company type then to each
>view add a trigger that would set the correct company type when a new record
>is inserted.
>
>If this is not correct or best strategy then please let me know.
>
>Thanks
>
>Regards
>
>"Ekrem Önsoy" <ekrem@compecta.com> wrote in message
>news38CE2B9-DBE7-450E-83B7-AEACCCF7B3B5@microsoft.com...
>> If this is something fixed, I mean if every inserted record's "Company
>> Type" value will be "Client" then why do you intend to use Triggers?
>>
>> You can make your application insert this value programmatically. Or you
>> can use DEFAULT constraint for "Company Type" field.
>>
>> Will you tell us more about this INSERT operation? Then we may suggest
>> something more practical.
>>
>> --
>> Ekrem Önsoy
>>
>>
>>
>> "John" <info@nospam.infovis.co.uk> wrote in message
>> news:uaaeqKD6IHA.1468@TK2MSFTNGP05.phx.gbl...
>>> Hi
>>>
>>> I am new to triggers. I have checked the syntax of triggers but it is
>>> very complicated for a newbie like me. What is the syntax of a trigger
>>> that will once a new record is inserted into the Contacts table, sets the
>>> value of the column 'Company Type' to Client in the inserted record?
>>>
>>> Thanks
>>>
>>> Regards
>>>
>>>
>>
>

On Thu, 17 Jul 2008 19:07:35 +0100, "John" <info@nospam.infovis.co.uk>
wrote:

>The solution I am trying (being a newbie) is to cerate views Client
>Contacts, Supplier Contacts and so on with each company type then to each
>view add a trigger that would set the correct company type when a new record
>is inserted.

Note what the Books on Line says about putting a trigger on a view:

"A view can be referenced only by an INSTEAD OF trigger."

The trigger example you got earlier was not an INSTEAD OF trigger.

Roy Harvey
Beacon Falls, CT