You have three tables that contain data for dentists, psychiatrists, and physicians. You create a viewthat is
used to look up their email addresses and phone numbers. The view has the following definition:
You need to ensure that users can update only the phone numbers and email addresses by using this view.
What should you do?
A.
Alter the view. Use the EXPAND VIEWS query hint along with each SELECT statement.
B.
Create an INSTEAD OF UPDATE trigger on the view.
C.
Drop the view. Re-create the view by using the SCHEMABINDING clause, and then create an index on the
view.
D.
Create an AFTER UPDATE trigger on the view.
Explanation:
Verified answer as correct.
Reference: http://msdn.microsoft.com/en-us/library/ms187956.aspx
B. INSTEAD OF UPDATE TRIGGER
0
0
create Procedure SpGenerateInsert(@tblname varchar(100))
As
Begin
SET NOCOUNT ON
DECLARE @table_name SYSNAME
DECLARE @handle_big_binary BIT
DECLARE @column_names BIT
— ////////////////////
— -> Configuration
SET @table_name = @tblname
SET @handle_big_binary = 1
SET @column_names = 1
— = @pos)
BEGIN
DECLARE @column_name SYSNAME
DECLARE @data_type SYSNAME
DECLARE @data_length INT
DECLARE @is_nullable BIT
— Get information for the current column
SELECT @column_name = column_name, @data_type = data_type, @data_length = data_length, @is_nullable = is_nullable
FROM @columns
WHERE ordinal_position = @pos
— Create column select information to script the name of the source/destination column if configured
IF (@select IS NULL)
SET @select = ‘ ”’ + QUOTENAME(@column_name)
ELSE
SET @select = @select + ‘,” + ‘ + @crlf + ‘ ”’ + QUOTENAME(@column_name)
— Handle NULL values
SET @sql = ‘ ‘
SET @sql = @sql + ‘CASE WHEN ‘ + QUOTENAME(@column_name) + ‘ IS NULL THEN ”NULL” ELSE ‘
— Handle the different data types
IF (@data_type IN (‘bigint’, ‘bit’, ‘decimal’, ‘float’, ‘int’, ‘money’, ‘numeric’,
‘real’, ‘smallint’, ‘smallmoney’, ‘tinyint’,’geography’,’datetime’,’time’))
BEGIN
SET @sql = @sql + ‘CONVERT(VARCHAR(max), ‘ + QUOTENAME(@column_name) + ‘)’
END
ELSE IF (@data_type IN (‘char’, ‘nchar’, ‘nvarchar’, ‘varchar’))
BEGIN
SET @sql = @sql + ””””’ + REPLACE(‘ + QUOTENAME(@column_name) + ‘, ””””, ””””””) + ””””’
END
ELSE IF (@data_type = ‘date’)
BEGIN
SET @sql = @sql + ”’CONVERT(DATE, ” + master.sys.fn_varbintohexstr (CONVERT(BINARY(3), ‘ + QUOTENAME(@column_name) + ‘)) + ”)”’
END
ELSE IF (@data_type = ‘datetime’)
BEGIN
SET @sql = @sql + ”’CONVERT(DATE, ” + master.sys.fn_varbintohexstr (CONVERT(BINARY(3), ‘ + QUOTENAME(@column_name) + ‘)) + ”)”’
END
— ELSE IF (@data_type = ‘time’)
–BEGIN
— SET @sql = @sql + ”’CONVERT(DATE, ” + master.sys.fn_varbintohexstr (CONVERT(BINARY(3), ‘ + QUOTENAME(@column_name) + ‘)) + ”)”’
–END
ELSE IF (@data_type = ‘geography’)
BEGIN
SET @sql = @sql + ”’CONVERT(GEOGRAPHY, ” + master.sys.fn_varbintohexstr (CONVERT(BINARY(5), ‘ + QUOTENAME(@column_name) + ‘)) + ”)”’
END
ELSE IF (@data_type = ‘time’)
BEGIN
SET @sql = @sql + ”’CONVERT(TIME, ” + master.sys.fn_varbintohexstr (CONVERT(BINARY(5), ‘ + QUOTENAME(@column_name) + ‘)) + ”)”’
END
ELSE IF (@data_type = ‘datetime’)
BEGIN
SET @sql = @sql + ”’CONVERT(DATETIME, ” + master.sys.fn_varbintohexstr (CONVERT(BINARY(8), ‘ + QUOTENAME(@column_name) + ‘)) + ”)”’
END
ELSE IF (@data_type = ‘datetime2’)
BEGIN
SET @sql = @sql + ”’CONVERT(DATETIME2, ” + master.sys.fn_varbintohexstr (CONVERT(BINARY(8), ‘ + QUOTENAME(@column_name) + ‘)) + ”)”’
END
ELSE IF (@data_type = ‘smalldatetime’)
BEGIN
SET @sql = @sql + ”’CONVERT(SMALLDATETIME, ” + master.sys.fn_varbintohexstr (CONVERT(BINARY(4), ‘ + QUOTENAME(@column_name) + ‘)) + ”)”’
END
ELSE IF (@data_type = ‘text’)
BEGIN
SET @sql = @sql + ””””’ + REPLACE(CONVERT(VARCHAR(MAX), ‘ + QUOTENAME(@column_name) + ‘), ””””, ””””””) + ””””’
END
ELSE IF (@data_type IN (‘ntext’, ‘xml’))
BEGIN
SET @sql = @sql + ””””’ + REPLACE(CONVERT(NVARCHAR(MAX), ‘ + QUOTENAME(@column_name) + ‘), ””””, ””””””) + ””””’
END
ELSE IF (@data_type IN (‘binary’, ‘varbinary’))
BEGIN
— Use udf_varbintohexstr_big if available to avoid cutted binary data
IF (@handle_big_binary = 1)
SET @sql = @sql + ‘ dbo.udf_varbintohexstr_big (‘ + QUOTENAME(@column_name) + ‘)’
ELSE
SET @sql = @sql + ‘ master.sys.fn_varbintohexstr (‘ + QUOTENAME(@column_name) + ‘)’
END
ELSE IF (@data_type = ‘timestamp’)
BEGIN
SET @sql = @sql + ”’CONVERT(TIMESTAMP, ” + master.sys.fn_varbintohexstr (CONVERT(BINARY(8), ‘ + QUOTENAME(@column_name) + ‘)) + ”)”’
END
ELSE IF (@data_type = ‘uniqueidentifier’)
BEGIN
SET @sql = @sql + ”’CONVERT(UNIQUEIDENTIFIER, ” + master.sys.fn_varbintohexstr (CONVERT(BINARY(16), ‘ + QUOTENAME(@column_name) + ‘)) + ”)”’
END
ELSE IF (@data_type = ‘image’)
BEGIN
— Use udf_varbintohexstr_big if available to avoid cutted binary data
IF (@handle_big_binary = 1)
SET @sql = @sql + ‘ dbo.udf_varbintohexstr_big (CONVERT(VARBINARY(MAX), ‘ + QUOTENAME(@column_name) + ‘))’
ELSE
SET @sql = @sql + ‘ master.sys.fn_varbintohexstr (CONVERT(VARBINARY(MAX), ‘ + QUOTENAME(@column_name) + ‘))’
END
ELSE
BEGIN
PRINT ‘ERROR: Not supported data type: ‘ + @data_type
RETURN
END
SET @sql = @sql + ‘ END’
— Script line end for finish or next column
IF EXISTS (SELECT TOP 1 * FROM @columns WHERE ordinal_position > @pos)
SET @sql = @sql + ‘ + ”, ” +’
ELSE
SET @sql = @sql + ‘ + ‘
— Remember the data script
IF (@insert IS NULL)
SET @insert = @sql
ELSE
SET @insert = @insert + @crlf + @sql
SET @pos = @pos + 1
END
— Close the column names select
SET @select = @select + ”’ +’
— Print the INSERT INTO part
Declare @a varchar(1000),@b varchar(1000),@c varchar(1000),@d varchar(1000),
@e varchar(1000),@f varchar(1000),@g varchar(1000),@h varchar(1000),@i varchar(8000)
set @a= ‘SELECT ”INSERT INTO ‘ + @table_name + ”’ + ‘
— Print the column names if configured
IF (@column_names = 1)
BEGIN
set @b= ‘ ”(” + ‘
set @c= @select
set @d= ‘ ”)” + ‘
END
set @e= ‘ ”VALUES (” +’
— Print the data scripting
set @f= @insert
— Script the end of the statement
set @g= ‘ ”)”’
set @h= ‘ FROM ‘ + @table_name
set @i=@a+@b+@c+@d+@e+@f+@g+@h
exec (@i)
End
0
0