CREATE TRIGGER [tr_gwAccount_INSERT]
ON [dbo].[GW_Account]
FOR INSERT
AS
DECLARE @variable VARCHAR(100) 
Select @variable  = (SELECT Company_Name FROM inserted)
IF NOT EXISTS (SELECT * FROM Customer_Profile WHERE Company_Name = ' + @variable + ')
 BEGIN
	Insert into Customer_Profile(....)
 END

CREATE TRIGGER [tr_gwAccount_INSERT]
ON [dbo].[GW_Account]
FOR INSERT
AS
BEGIN
    Insert into Customer_Profile(....)
    select . .  .
    from   inserted i
    where  not exists (select * from Customer_Profile x where x.pkcol = i.pkcol)    
END

quote:

---

Originally posted by khtan

Your problem is not on the NOT EXISTS. Your trigger can only handle one record being inserted only. You should assume the inserted as a table that may contains more than 1 records.

CREATE TRIGGER [tr_gwAccount_INSERT]
ON [dbo].[GW_Account]
FOR INSERT
AS
BEGIN
    Insert into Customer_Profile(....)
    select . .  .
    from   inserted i
    where  not exists (select * from Customer_Profile x where x.pkcol = i.pkcol)    
END

take a look at http://www.sqlteam.com/item.asp?ItemID=3850

---

KH

---

quote:

---

Inserting a Row Using INSERT...Values
The VALUES keyword specifies the values for one row of a table. The values are specified as a comma-separated list of scalar expressions whose data type, precision, and scale must be the same as or implicitly convertible to the corresponding column in the column list. If a column list is not specified, the values must be specified in the same sequence as the columns in the table or view.

For example, this statement inserts a new shipper into the Shippers table using the VALUES clause:

INSERT INTO Northwind.dbo.Shippers (CompanyName, Phone)
VALUES (N'Snowflake Shipping', N'(503)555-7233')

---

quote:

---

Inserting Rows Using INSERT...SELECT
The SELECT subquery in the INSERT statement can be used to add values into a table from one or more other tables or views. Using a SELECT subquery also lets more than one row be inserted at one time.

This INSERT statement inserts into a separate table some of the data from all the rows in titles whose type is modern cooking:

USE pubs
INSERT INTO MyBooks
SELECT title_id, title, type
FROM titles
WHERE type = 'mod_cook'

The select list of the subquery must match the column list of the INSERT statement. If no column list is specified, the select list must match the columns in the table or view being inserted into.

---

quote:

---

Originally posted by mahesh_bote

quote:

---

Originally posted by khtan

Your problem is not on the NOT EXISTS. Your trigger can only handle one record being inserted only. You should assume the inserted as a table that may contains more than 1 records.

CREATE TRIGGER [tr_gwAccount_INSERT]
ON [dbo].[GW_Account]
FOR INSERT
AS
BEGIN
    Insert into Customer_Profile(....)
    select . .  .
    from   inserted i
    where  not exists (select * from Customer_Profile x where x.pkcol = i.pkcol)    
END

take a look at http://www.sqlteam.com/item.asp?ItemID=3850

---

KH

---

hi khtan,

i think there is no need to check condition like NOT EXISTS, but when that guy has PK on his table. Because we have written trigger for insert. Then if there is no any duplication, it will not insert any record into Customer_Profile, and hence there will be no issue for record existanct in Customer_Table.

Hopes i have explained well.

let me know, if i m wrong,


Mahesh

---

quote:

---

Originally posted by mahesh_bote

when u create any trigger, two magic tables are created. we can't view them. they are INSERTED and DELETED. when we write trigger for Insert the same insert statement will be inserted in SQL Server will add the newly inserted records into the magic table, named INSERTED. the same thing happen in case of deletion. it adds one entry the deleted records into DELETED table. thats why u have to select values from INSERTED table and insert them into new / desired table.

let me know, if i m wrong,

Mahesh

---

quote:

---

Originally posted by khtan

quote:

---

Originally posted by mahesh_bote

when u create any trigger, two magic tables are created. we can't view them. they are INSERTED and DELETED. when we write trigger for Insert the same insert statement will be inserted in SQL Server will add the newly inserted records into the magic table, named INSERTED. the same thing happen in case of deletion. it adds one entry the deleted records into DELETED table. thats why u have to select values from INSERTED table and insert them into new / desired table.

let me know, if i m wrong,

Mahesh

---

And for UPDATE operation, the affected records will be in inserted (after) and deleted (before) table.

---

KH

---