create table table1
(
	col1	int,
	col2	int,
	col3	int
)

create trigger tu_table1 on table1 for update
as
begin
	if update (col2)
	begin
		RAISERROR ('These columns should never be updated', 16, 1)
		ROLLBACK TRANSACTION
	end
end

insert into table1
select	1, 1, 1	union all
select  2, 2, 2 union all
select  3, 3, 3

select * from table1
Result
======
col1    col2    col3
1	1	1
2	2	2
3	3	3

update table1 set col3 = 30 where col1 = 3

select * from table1
Result
======
col1    col2    col3
1	1	1
2	2	2
3	3	30

update table1 set col2 = 3
Result
======
Server: Msg 50000, Level 16, State 1, Procedure tu_table1, Line 6
These columns should never be updated

quote:

---

for that particular column i have various value and various update is going to happen, so there is possible for raise trigger which will be waste]

---

quote:

---

SELECT @sLocation = Location FROM INSERTED

---

ALTER TRIGGER trgSR_CheckAndAddSampleRequest_Mill1
ON NYS1ReHeat FOR UPDATE
AS
BEGIN
	DECLARE @nHeat 		AS INT,
		@nRollMillBarID AS INT,
		@sProduct 	AS VARCHAR(20),
		@nSRCount 	AS INT,
		@sLocation 	AS VARCHAR(20)

--	SELECT @nHeat = Heat, @nRollMillBarID = LocSeq, @sProduct = FinalProd, @sLocation = Location FROM INSERTED
-- 	REMOVED. This only handled one row not multiple row

--	IF (@sLocation = 'REHEAT')
--	BEGIN
--		IF NOT EXISTS (SELECT * FROM Physicals WHERE Heat = @nHeat AND SQLProduct = @sProduct)
		IF NOT EXISTS (SELECT * FROM Physicals p inner join inserted i
					on	p.Heat		= i.Heat
					and	p.SQLProduct	= i.FinalProd
					and	i.Location	= 'REHEAT')
		BEGIN
--			SELECT 	@nSRCount = COUNT(*)
--			FROM 	SampleRequest SR INNER JOIN NYS1Reheat Reheat 
--				ON 	SR.RollMillBarID = Reheat.LocSeq
--			WHERE 	Reheat.Heat 	= @nHeat 
--			AND 	Reheat.FinalProd = @sProduct 
--			AND 	(SR.SampleRequest = 1 OR SR.SampleCut=1)
			SELECT	@nSRCount = COUNT(*)
			FROM	inserted i inner join NYS1Reheat Reheat 
				on	i.Heat		= Reheat.Heat
				and	i.FinalProd	= Reheat.FinalProd
				inner join SampleRequest SR
				on	SR.RollMillBarID = Reheat.LocSeq
			WHERE	(SR.SampleRequest = 1 OR SR.SampleCut = 1)
			AND	i.Location	= 'REHEAT'

			IF (@nSRCount < 2)
			BEGIN
--				INSERT INTO SampleRequest(RollMillBarID, SampleRequest, SampleCut, AutoRequest) VALUES(@nRollMillBarID, 1, 0, 1)
				INSERT INTO SampleRequest(RollMillBarID, SampleRequest, SampleCut, AutoRequest)
				SELECT	i.LocSeq, 1, 0, 1
				FROM	inserted i
				WHERE	i.Location	= 'REHEAT')
			END
		END
--	END
END

quote:

---

INSERT INTO SampleRequest(RollMillBarID, SampleRequest, SampleCut, AutoRequest)
SELECT i.LocSeq, 1, 0, 1 FROM inserted

i think the same think i used in the above statement like this

SELECT @LocSeq = LocSeq FROM inserted

---

quote:

---

Then when will be the inserted table get cleared.

---

quote:

---

i think this inserted table created for every trigger is that right

---

quote:

---

i think the inserted table conatin only the current value which is going to be insert

---

quote:

---

The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added simultaneously to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.

---

ALTER TRIGGER trgSR_CheckAndAddSampleRequest_Mill1
	ON NYS1ReHeat FOR UPDATE
AS
BEGIN
	DECLARE @nHeat 		AS INT,
		@nRollMillBarID AS INT,
		@sProduct 	AS VARCHAR(20),
		@nSRCount 	AS INT,
		@sLocation 	AS VARCHAR(20)

	IF UPDATE(Location)
	BEGIN
		SELECT @nRollMillBarID = ins.LocSeq FROM INSERTED ins WHERE ins.Location = 'REHEAT'
		IF NOT EXISTS (SELECT * FROM SampleRequest WHERE RollMillBarID = @nRollMillBarID)
			IF NOT EXISTS (SELECT * FROM Physicals phy 
					INNER JOIN Inserted ins 
						ON 	phy.Heat 	= ins.Heat
						AND 	phy.SQLProduct 	= ins.FinalProd 
--						AND 	ins.Location 	= 'REHEAT')
					WHERE 	ins.Location 	= 'REHEAT')  -- Preferable joining condition in ON and other in WHERE
			BEGIN
				SELECT 	@nSRCount = COUNT(*)
					FROM 	Inserted ins 
					INNER JOIN NYS1Reheat Reheat
						ON 	ins.Heat 	= Reheat.Heat
						AND 	ins.FinalProd 	= Reheat.FinalProd
					INNER JOIN SampleRequest SR
						ON 	SR.RollMillBarID = Reheat.LocSeq
				WHERE 	(SR.SampleRequest = 1 OR SR.SampleCut = 1)
				AND 	ins.Location = 'REHEAT'

				IF (@nSRCount < 2)
				BEGIN
					INSERT INTO SampleRequest(RollMillBarID, SampleRequest, SampleCut, AutoRequest)
					SELECT 	ins.LocSeq, 1, 0, 1
					FROM 	inserted ins
					WHERE 	ins.Location = 'REHEAT'
				END
			END
	END
END

quote:

---

maximum number of rows in the instered table is 2

---