Set NoCount On

Create load_pounds_detail(ticket_number_detail varchar(10), pickup_date_detail datetime, load_pounds_detail int)

Insert into load_pounds_detail
   Select Distinct ticket_number, pickup_date, load_pounds
If Exists(Select * From load_pounds_detail Group by ticket_number_detail , pickup_date_detail
Having Sum(1)>1)
Begin    
Return
End

CREATE TRIGGER trg_name ON table
AFTER INSERT,UPDATE,DELETE
AS
BEGIN

IF EXISTS (SELECT * FROM INSERTED)
AND NOT EXISTS(SELECT * FROM DELETED)
BEGIN
INSERT INTO table2
SELECT your fields 
FROM INSERTED i
END

IF EXISTS (SELECT * FROM INSERTED)
AND EXISTS(SELECT * FROM DELETED)
BEGIN
UPDATE t
SET t.field1=i.field1,t.field2=i.field2,...
FROM table2 t
INNER JOIN INSERTED i
ON i.FKCol=t.PKCol
END

IF NOT EXISTS (SELECT * FROM INSERTED)
AND EXISTS(SELECT * FROM DELETED)
BEGIN
DELETE t 
FROM table2 t
INNER JOIN DELETED d
ON d.FKCol=t.PKCol
END

IF NOT EXISTS (SELECT * FROM INSERTED)
AND EXISTS(SELECT * FROM DELETED)
BEGIN
DELETE t 
FROM table2 t
INNER JOIN DELETED d
ON d.FKCol=t.PKCol
END

IF NOT EXISTS (SELECT * FROM INSERTED)
AND EXISTS(SELECT * FROM DELETED)
BEGIN
DELETE ticket_number 
FROM detail_record.ticket_number
INNER JOIN DELETED d
ON detail_record.ticket_number=load_pound_detail.ticket_number
END

quote:

---

I should end up with :
IF NOT EXISTS (SELECT * FROM INSERTED)
AND EXISTS(SELECT * FROM DELETED)
BEGIN
DELETE ticket_number
FROM detail_record.ticket_number
INNER JOIN DELETED d
ON detail_record.ticket_number=load_pound_detail.ticket_number
END

---

IF NOT EXISTS (SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED) BEGIN
	DELETE r
	FROM
		detail_record r
		INNER JOIN DELETED d ON r.[ticket_number] = d.[ticket_number]
END

IF EXISTS (SELECT * FROM INSERTED)
AND NOT EXISTS(SELECT * FROM DELETED)
BEGIN
INSERT INTO adjust_load_pounds
SELECT DISTINCT detail_record.ticket_number, detail_record.pickup_dt, detail_record.ddp_weight
FROM INSERTED  
END

quote:

---

Msg 4104, Level 16, State 1, Procedure trg_adjust_load_pounds, Line 14
The multi-part identifier "detail_record.ticket_number" could not be bound.
Msg 4104, Level 16, State 1, Procedure trg_adjust_load_pounds, Line 14
The multi-part identifier "detail_record.pickup_dt" could not be bound.
Msg 4104, Level 16, State 1, Procedure trg_adjust_load_pounds, Line 14
The multi-part identifier "detail_record.ddp_weight" could not be bound.

---

quote:

---

Originally posted by CoachBarker

Is there a way to do an insert like this?

IF EXISTS (SELECT * FROM INSERTED)
AND NOT EXISTS(SELECT * FROM DELETED)
BEGIN
INSERT INTO adjust_load_pounds
SELECT DISTINCT detail_record.ticket_number, detail_record.pickup_dt, detail_record.ddp_weight
FROM INSERTED  
END

I got this as an error:

quote:

---

Msg 4104, Level 16, State 1, Procedure trg_adjust_load_pounds, Line 14
The multi-part identifier "detail_record.ticket_number" could not be bound.
Msg 4104, Level 16, State 1, Procedure trg_adjust_load_pounds, Line 14
The multi-part identifier "detail_record.pickup_dt" could not be bound.
Msg 4104, Level 16, State 1, Procedure trg_adjust_load_pounds, Line 14
The multi-part identifier "detail_record.ddp_weight" could not be bound.

---

Thanks for the help
CoachBarker

---

IF EXISTS (SELECT * FROM INSERTED)
AND NOT EXISTS(SELECT * FROM DELETED)
BEGIN
INSERT INTO adjust_load_pounds
SELECT DISTINCT ticket_number, pickup_dt, ddp_weight
FROM INSERTED  
END

-- Make up a table variable to show UPDATE / SELECT / DELETE ops on
-- Table varialbes *must* be referenced via an alias
-- They cannot be referenced directly except in the most simple case
DECLARE @foo TABLE (
	[Id] INT IDENTITY (1,1) PRIMARY KEY
	, [valA] VARCHAR(50)
	, [valB] MONEY
	)

-- Table varialbe @bar -- the fooId will be a link to @foo
-- in a base table you'd enforce this with a foreign key.
DECLARE @bar TABLE (
	[fooId] INT
	, [val] DATETIME
	)

-- Populate the @foo Table
-- Alias(s) are useless for inserts they always use proper names
INSERT INTO @foo (
	[valA]
	, [valB]
	)
      SELECT 'a', 100
UNION SELECT 'b', 200
UNION SELECT 'c', 300
UNION SELECT 'd', 400

-- Populate the @bar table
INSERT INTO @bar (
	[fooID]
	, [val]
	)
      SELECT 1, '2009-01-01T23:59:59'
UNION SELECT 2, '2007-06-04T12:00:00'
UNION SELECT 3, '2009-04-01T00:00:00'

-- Show the @foo Table (note here not using an alias)
SELECT '@foo after INSERTS', * FROM @foo ORDER BY [valA] ASC

-- Show the @bar Table (using an alias -- you would never want to do this for real)
-- The AS keyword can also be used betweent he alias and the table, it's not needed and most
-- people leave it out. You tend to see the AS more in derived tables.
-- Both these SELECT statements are the same
SELECT '@bar Alias No AS', b.[fooID], b.[val] FROM @bar b
SELECT '@bar Alias using AS', b.[fooId], b.[val] FROM @bar AS b

-- SELECT example using LEFT JOIN and aliases
-- I tend to use the AS keyword when changing the returned column names in the result set
-- Example -- from f.[ID] to [fooId]
-- but I never use the AS keywords for aliases (this is just personal preference though)
SELECT
	'LEFT JOIN @Bar Example'
	, f.[Id] AS [FooId]
	, b.[val] AS [Effective Date]
	, f.[valb] AS [Cost]
FROM
	@foo f
	-- Going to LEFT JOIN @bar here on @bar.[fooId] = @foo.[Id]
	-- Because these are table variables we can't actually do that without aliases
	LEFT JOIN @bar b ON b.[fooId] = f.[Id]

-- UPDATE example using aliases (show the @foo table before and after)
-- This updates @foo valA column to 'X' and valb column to 0 where the corrospnding @Bar
-- data is in 2009
SELECT 'Before UPDATE', * FROM @foo

UPDATE f SET
	[valA] = 'X'
	, [valB] = 000.00
FROM
	@foo f
	JOIN @bar b ON b.[fooId] = f.[ID]
WHERE
	b.[val] >= '2009-01-01T00:00:00' AND b.[val] < '2010-01-01T00:00:00'

SELECT 'After UPDATE', * FROM @foo

-- DELETE example using aliases (show the @foo table before and after)
-- We are going to delete every row in @foo that does not have a corrosponding entry in @bar
-- Remember that the WHERE clause is evaluated AFTER the JOINS have been resolved.
SELECT 'Before DELETE', * FROM @foo

DELETE f
FROM
	@foo f
	LEFT JOIN @bar b ON b.[fooId] = f.[Id]
WHERE
	b.[fooId] IS NULL

SELECT 'After DELETE', * FROM @foo