Prev: sproc question
Next: Stored proc issue after converting from Sql Server 2000 to 2005.
From: John on 17 Jul 2008 13:29
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


From: Denny Cherry on 17 Jul 2008 13:42
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(a)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
>
From: John on 17 Jul 2008 13:47
Hi Denny

Many thanks. This has made it very clear.

Regards

"Denny Cherry" <mrdenny.nospam(a)mrdenny.com> wrote in message
news:6u0v74trhbucv09cq200c5u8g1jc2eed4h(a)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(a)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
>>


From: Ekrem Önsoy on 17 Jul 2008 13:47
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(a)nospam.infovis.co.uk> wrote in message
news:uaaeqKD6IHA.1468(a)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
>
>

From: John on 17 Jul 2008 14:07
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(a)compecta.com> wrote in message
news:D38CE2B9-DBE7-450E-83B7-AEACCCF7B3B5(a)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(a)nospam.infovis.co.uk> wrote in message
> news:uaaeqKD6IHA.1468(a)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
>>
>>
>


 |  Next  |  Last
Pages: 1 2
Prev: sproc question
Next: Stored proc issue after converting from Sql Server 2000 to 2005.