August 24, 2001 at 5:41 am
How to build dynamic TSQL statement in an instead of trigger for inserting values from inserted to base table?
A view with instead of trigger is great tool for security. But there are some problems. For example if the instead of trigger defined on a view inserts all columns to base table, the base table defaults doesn't take effect. I want to build insert statement for just an updated columns.
A little difficult work around:
--drop table [dbo].[Sample_table]
--GO
CREATE TABLE [dbo].[Sample_table] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[char_column] [char] (10) NOT NULL ,
[date_column] [datetime] NULL ,
[money_column] [money] NULL ,
[timestamp_column] [timestamp] NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Sample_table ADD CONSTRAINT
DF_Sample_table_date_column DEFAULT getdate() FOR date_column
GO
ALTER TABLE dbo.Sample_table ADD CONSTRAINT
DF_Sample_table_money_column DEFAULT 1000 FOR money_column
GO
-- sample rows
INSERT INTO sample_table (char_column) VALUES ('test1')
INSERT INTO sample_table (char_column,date_column) VALUES ('test2',getdate()-70)
INSERT INTO sample_table (char_column,money_column) VALUES ('test3',3000)
GO
-- DROP VIEW sample_view
-- GO
CREATE VIEW sample_view AS
SELECT ID, char_column, date_column,
money_column=case is_member('leader')
WHEN 1 THEN money_column
ELSE null
END, timestamp_column
FROM dbo.Sample_table
WHERE NOT is_member('h_restricted')=1 OR datediff(day, date_column, getdate())<60
-- really secure, isn't it ? 🙂
go
CREATE TRIGGER tr_sample_view_i ON sample_view INSTEAD OF INSERT
AS
INSERT INTO sample_table (char_column, date_column, money_column)
SELECT char_column, date_column, money_column FROM inserted
go
-- sample row, must put some dummy value to ID, because it is not NULLable in view
INSERT INTO sample_view (ID, char_column) VALUES (0,'test5')
-- puts NULL value to the date_column,
-- because the trigger inserts all columns, default getdate() doesn't take effect
go
DROP TRIGGER tr_sample_view_i
go
CREATE TRIGGER tr_sample_view_i ON sample_view INSTEAD OF INSERT
AS
BEGIN
DECLARE @clist nvarchar(200), @tsqls nvarchar(200)
SET @clist=''
--creating updated column list
if update(char_column)
SET @clist='char_column,'
IF update(date_column)
SET @clist=@clist+'date_column,'
IF update(money_column)
SET @clist=@clist+'money_column,'
IF len(@clist)=0
EXEC sp_sqlexec N'insert into dbo.sample_table default values'
ELSE
BEGIN
-- cutting trailing comma
SET @clist=left(@clist,len(@clist)-1)
SET @tsqls='insert into dbo.sample_table ('+@clist+') select '+@clist+' from inserted'
EXEC sp_sqlexec @tsqls
-- it wont work, because sqlexec fires another batch, and inserted isn't visible out there
-- but how to make it useable ?
END
END
GO
August 25, 2001 at 11:55 am
Not sure at first glance...I'll get back to you.
August 28, 2001 at 6:58 pm
sorry its taken so long...still looking though. I'll try and get an answer tomorrow.
Thanks
August 29, 2001 at 11:30 am
I'm not exactly understanding what your not able to accomplish. Could you supply me with a little more info.
Thanks
December 5, 2002 at 9:28 pm
quote:
How to build dynamic TSQL statement in an instead of trigger for inserting values from inserted to base table?
This isn't exactly what you want but will accomplish it nonetheless. Inside your trigger you canuse the "IF UPDATE (column_name) clause " to determine if a column was updated. code something like this:
declare @MyName varchar(80)
if Update(Name) select @Myname=inserted.Name
... repeat as necessary
update othertable
set name=IsNull(@Myname,Name)
... all columns
This updates your audit table (?) with all updated columns and leaves unmodified colums alone.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply